Log into hue and run the hive script run the hive script hue
Learning Basic Commands with Hive
Set the database as default with the following command:
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
LOCATION 'hdfs://10.0.2.15:8020/user/hive/warehouse';
The wlslog database is created, as shown below.
Set the database as the current database for subsequent HiveQL statements using the USE database_name command. For example, set the database to WLSLOG as follows.
USE WLSLOG;
3. Creating a managed table
Now you would create a managed table to store some data. The data is stored in a text file in HDFS and takes the following format; the complete data file is listed later. The fields in a row are delimited by a, (a comma) and each row is terminated with a newline character (/n).
Subsequently run the show tables command. The wlslog table should be listed.
Initially, the wlslog is empty. If you run a SELECT query statement on the wlslog table, no rows would be listed.
Initially the managed table wlslog is empty. You will load data into the wlslog table. Create a file called wlslog.txt and copy the following data into that file.
Apr-8-2014-7:06:16-PM-PDT,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to STANDBY
Apr-8-2014-7:06:21-PM-PDT,Notice,WebLogicServer,AdminServer,BEA-000365,Server state changed to RUNNING
Apr-8-2014-7:06:22-PM-PDT,Notice,WebLogicServer,AdminServer,BEA-000360,Server started in RUNNING mode
Data from the wlslog.txt file is loaded into the wlslog table.
Subsequently, run the following SELECT query on the wlslog table.
CREATE TABLE wlslog_2 LIKE wlslog;
The wlslog_2 table is created.
The following subsections illustrate how to add data to the empty table called wlslog_2 using each of the INSERT statements.
5.2 Adding data using INSERT OVERWRITE
DROP TABLE wlslog_2;
Run the following INSERT OVERWRITE statement to add data to the empty wlslog_2 table.
6. Creating a table using CREATE TABLE AS SELECT
Sometimes an existing table has the required data and you want to create a new table by selecting some or all of the data from the existing table. HiveQL provides the CREATE TABLE AS SELECT statement to create a table by selecting data from an existing table. The STORED AS clause in a CREATE TABLE statement specifies that the data be stored as a text file. The TEXTFILE is the default storage format. For compressed storage, use the SEQUENCEFILE file format. For ORC file format, use STORED AS ORC. For Parquet columnar format, use STORED AS PARQUET.
AS
SELECT time_stamp,category,type,servername,code,msg
As an example of ALTER TABLE, set the immutable property to true and alter a table comment for the wlslog_copy table.
ALTER TABLE wlslog_copy SET TBLPROPERTIES ("immutable"="trueVcomment' = 'table altered');
The TRUNCATE TABLE statement removes all rows from a table but does not drop the table. The syntax for the TRUNCATE TABLE statement is as follows.
TRUNCATE TABLE table_name [PARTITION partition_spec];
Because the table rows have been removed, no rows are listed.
9. Dropping a table
Subsequently run the SHOW TABLES command. The wlslog_copy table is not listed.
Run Hive Queries on a Publicly Available Dataset
set mapred.min.split.size=67108864;
set mapred.max.split.size=536870912;
set ngram=1gram;
CREATE EXTERNAL TABLE IF NOT EXISTS ngrams (
books bigint
)
DESCRIBE ngrams;
SELECT * FROM ngrams LIMIT 10;
);
Now you need to insert data into this table using a select query. You add min_year and max_year settings to your init section first.
lower(gram),
year,
year >= (${hiveconf:min_year} - 1) AND
year <= ${hiveconf:max_year} AND
To add Ganglia to a cluster using the console
1. Open the Amazon EMR console at https://console.aws.amazon.com/elasticmapreduce/.
5. Calculating the word usage ratios
In order to calculate trends, you want to work on relative occurrences of words compared to the total corpus by year. The following code creates another table, and then fills it with the data. Enter the following statement:
SELECT a.gram, a.year, sum(
a.occurrences) / b.total
GROUP BY year ) b
ON a.year = b.year
set outputbucket=s3n://emr-hive-output/output_table;
CREATE EXTERNAL TABLE IF NOT EXISTS output_table (
Finally, we calculate the difference of ratios year over year by joining the previous table with itself. Enter the following statement:
INSERT OVERWRITE TABLE output_table
ON a.gram = b.gram and a.year - 1 = b.year
WHERE a.ratio > 0.000001 and a.year >= ${hiveconf:min_year} and a.year <= ${hiveconf:max_year}
FROM output_table
WHERE year = 1977 LIMIT 100;
1. Type the following URL in your browser: http://localhost:8157.
At the Hue welcome page, type a Username and Password. The name and password used the first time you log into Hue become the Hue superuser credentials.
This HiveQL query retrieves the total requests per browser for a given time frame.
5. Choose Execute. As the query runs, log entries are displayed on the Log tab in the window below. When the query completes, the Results tab is displayed.
The SPLIT function takes a string and splits it by a delimiter or expression. The resultant output is an array of string elements.
EXPLODE Function
The POSEXPLODE function works similarly to the EXPLODE function but returns the element along with its position (or index) in the input array.
Practice in Hive’s built-in text processing functions:
1. Create a table in Hive for the shakespeare dataset loaded into HDF.
CREATE EXTERNAL TABLE shakespeare (text STRING)
SELECT word, COUNT(*)
FROM shakespeare
Provide write your solution for this exercise.
Now you complete this lab. Before you go, please don’t forget to terminate your cluster to avoid unnecessary charges. You can do this easily in the EMR console by selecting the active job flow and clicking on terminate. You can also delete any S3 buckets you have created and
