Home > Storage > PowerScale (Isilon) > Industry Solutions and Verticals > Analytics > Multi-Cloud Data Services for Dell PowerScale in AWS: Amazon EMR for Data Analytics Solutions > Apache Hive and Hive on TEZ
Script started on 2021-06-03 17:25:04+0000
EEEEEEEEEEEEEEEEEEEE MMMMMMMM MMMMMMMM RRRRRRRRRRRRRRR
E::::::::::::::::::E M:::::::M M:::::::M R::::::::::::::R
EE:::::EEEEEEEEE:::E M::::::::M M::::::::M R:::::RRRRRR:::::R
E::::E EEEEE M:::::::::M M:::::::::M RR::::R R::::R
E::::E M::::::M:::M M:::M::::::M R:::R R::::R
E:::::EEEEEEEEEE M:::::M M:::M M:::M M:::::M R:::RRRRRR:::::R
E::::::::::::::E M:::::M M:::M:::M M:::::M R:::::::::::RR
E:::::EEEEEEEEEE M:::::M M:::::M M:::::M R:::RRRRRR::::R
E::::E M:::::M M:::M M:::::M R:::R R::::R
E::::E EEEEE M:::::M MMM M:::::M R:::R R::::R
EE:::::EEEEEEEE::::E M:::::M M:::::M R:::R R::::R
E::::::::::::::::::E M:::::M M:::::M RR::::R R::::R
EEEEEEEEEEEEEEEEEEEE MMMMMMM MMMMMMM RRRRRRR RRRRRR
]0;hadoop@ip-10-15-1-140:~/scripts
[?1034h[hadoop@ip-10-15-1-140 scripts]$ ./hive_dll.sh
++ clear
[3J[H[2J++ cat
++ sudo -u hive beeline -u 'jdbc:hive2://localhost:10000/default;' -n hive -p hive -f /tmp/hive_ddl.sql
Connecting to jdbc:hive2://localhost:10000/default;
Connected to: Apache Hive (version 2.3.7-amzn-4)
Driver: Hive JDBC (version 2.3.7-amzn-4)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000/default> -- Create remote DB DDL
0: jdbc:hive2://localhost:10000/default> SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxn
Manager;
No rows affected (0.062 seconds)
0: jdbc:hive2://localhost:10000/default> SET hive.support.concurrency=true;
No rows affected (0.004 seconds)
0: jdbc:hive2://localhost:10000/default> SET hive.enforce.bucketing=true;
No rows affected (0.003 seconds)
0: jdbc:hive2://localhost:10000/default> SET hive.exec.dynamic.partition.mode=nonstrict;
No rows affected (0.003 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> DROP DATABASE IF EXISTS remote_DB CASCADE;
No rows affected (0.747 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE database remote_DB COMMENT 'Holds all the tables data
in remote location Isilon Hadoop cluster' LOCATION 'hdfs://10.1.1.15:8020/user/hive/remote_DB' ;
No rows affected (0.051 seconds)
0: jdbc:hive2://localhost:10000/default> USE remote_DB;
No rows affected (0.024 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE TABLE passwd_int_nonpart
. . . . . . . . . . . . . . . . . . . .> (user_name STRING, password STRING, user_id STRING, group_id
STRING, user_id_info STRING, home_dir STRING, shell STRING)
. . . . . . . . . . . . . . . . . . . .> ROW FORMAT DELIMITED FIELDS TERMINATED BY ':'
. . . . . . . . . . . . . . . . . . . .> STORED AS TEXTFILE;
No rows affected (0.073 seconds)
0: jdbc:hive2://localhost:10000/default> LOAD data local inpath '/etc/passwd' into TABLE passwd_int_
nonpart;
No rows affected (0.232 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE EXTERNAL TABLE passwd_ext_nonpart_remote
. . . . . . . . . . . . . . . . . . . .> (user_name STRING, password STRING, user_id STRING, group_id
STRING, user_id_info STRING, home_dir STRING, shell STRING)
. . . . . . . . . . . . . . . . . . . .> ROW FORMAT DELIMITED FIELDS TERMINATED BY ':'
. . . . . . . . . . . . . . . . . . . .> STORED AS TEXTFILE
. . . . . . . . . . . . . . . . . . . .> LOCATION 'hdfs://10.1.1.15:8020/user/hive/remote_DB/passwd_e
xt_nonpart_remote';
No rows affected (0.102 seconds)
0: jdbc:hive2://localhost:10000/default> LOAD data local inpath '/etc/passwd' into TABLE passwd_ext_
nonpart_remote;
No rows affected (0.222 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE TABLE passwd_int_trans
. . . . . . . . . . . . . . . . . . . .> (user_name STRING, password STRING, user_id STRING, group_id
STRING, user_id_info STRING, home_dir STRING, shell STRING) CLUSTERED by(user_name) into 3 buckets s
tored as orc tblproperties ("transactional"="true");
No rows affected (0.104 seconds)
0: jdbc:hive2://localhost:10000/default> INSERT INTO TABLE passwd_int_trans SELECT user_name, passwor
d, user_id, group_id, user_id_info, home_dir, shell from passwd_int_nonpart;
No rows affected (10.795 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE TABLE passwd_int_part (user_name STRING, password STR
ING, user_id STRING, user_id_info STRING, home_dir STRING, shell STRING) PARTITIONED BY (group_id STR
ING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ':';
No rows affected (0.108 seconds)
0: jdbc:hive2://localhost:10000/default> INSERT OVERWRITE TABLE passwd_int_part PARTITION (group_id)
SELECT user_name, password, user_id, user_id_info, home_dir, shell, group_id from passwd_int_nonpart;
No rows affected (4.14 seconds)
0: jdbc:hive2://localhost:10000/default> --show partitions passwd_int_part;
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE EXTERNAL TABLE passwd_ext_nonpart (user_name STRING,
password STRING, user_id STRING, group_id STRING, user_id_info STRING, home_dir STRING, shell STRING)
. . . . . . . . . . . . . . . . . . . .> ROW FORMAT DELIMITED FIELDS TERMINATED BY ':' LOCATION 'hdfs
://10.1.1.15:8020/user/hive/remote_DB/passwd_ext_nonpart_remote';
No rows affected (0.085 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> -- Create local DB DDL
0: jdbc:hive2://localhost:10000/default> DROP DATABASE IF EXISTS local_DB CASCADE;
No rows affected (0.482 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE database local_DB COMMENT 'Holds all the tables data
in local Hadoop cluster' LOCATION '/user/hive/local_DB';
No rows affected (0.046 seconds)
0: jdbc:hive2://localhost:10000/default> USE local_DB;
No rows affected (0.022 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE TABLE passwd_int_nonpart
. . . . . . . . . . . . . . . . . . . .> (user_name STRING, password STRING, user_id STRING, group_id
STRING, user_id_info STRING, home_dir STRING, shell STRING)
. . . . . . . . . . . . . . . . . . . .> ROW FORMAT DELIMITED FIELDS TERMINATED BY ':'
. . . . . . . . . . . . . . . . . . . .> STORED AS TEXTFILE;
No rows affected (0.068 seconds)
0: jdbc:hive2://localhost:10000/default> LOAD data local inpath '/etc/passwd' into TABLE passwd_int_
nonpart;
No rows affected (0.201 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE EXTERNAL TABLE passwd_ext_nonpart_local
. . . . . . . . . . . . . . . . . . . .> (user_name STRING, password STRING, user_id STRING, group_id
STRING, user_id_info STRING, home_dir STRING, shell STRING)
. . . . . . . . . . . . . . . . . . . .> ROW FORMAT DELIMITED FIELDS TERMINATED BY ':'
. . . . . . . . . . . . . . . . . . . .> STORED AS TEXTFILE
. . . . . . . . . . . . . . . . . . . .> LOCATION 'hdfs://10.1.1.15:8020/user/hive/local_DB/passwd_ex
t_nonpart_local';
No rows affected (0.095 seconds)
0: jdbc:hive2://localhost:10000/default> LOAD data local inpath '/etc/passwd' into TABLE passwd_ext_
nonpart_local;
No rows affected (0.208 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE TABLE passwd_int_trans (user_name STRING, password ST
RING, user_id STRING, group_id STRING, user_id_info STRING, home_dir STRING, shell STRING) CLUSTERED
by(user_name) into 3 buckets stored as orc tblproperties ("transactional"="true");
No rows affected (0.102 seconds)
0: jdbc:hive2://localhost:10000/default> INSERT INTO TABLE passwd_int_trans SELECT user_name, passwor
d, user_id, group_id, user_id_info, home_dir, shell from passwd_int_nonpart;
No rows affected (1.943 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE TABLE passwd_int_part (user_name STRING, password STR
ING, user_id STRING, user_id_info STRING, home_dir STRING, shell STRING) PARTITIONED BY (group_id STR
ING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ':';
No rows affected (0.1 seconds)
0: jdbc:hive2://localhost:10000/default> INSERT OVERWRITE TABLE passwd_int_part PARTITION (group_id)
SELECT user_name, password, user_id, user_id_info, home_dir, shell, group_id from passwd_int_nonpart;
No rows affected (4.018 seconds)
0: jdbc:hive2://localhost:10000/default> --show partitions passwd_int_part;
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> CREATE EXTERNAL TABLE passwd_ext_nonpart (user_name STRING,
password STRING, user_id STRING, group_id STRING, user_id_info STRING, home_dir STRING, shell STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ':' LOCATION 'hdfs://10.1.1.15:8020/user/hive/local_DB/pas
swd_ext_nonpart_local';
No rows affected (0.082 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> --Check the database
0: jdbc:hive2://localhost:10000/default> SHOW databases;
+----------------+
| database_name |
+----------------+
| default |
| local_db |
| remote_db |
+----------------+
3 rows selected (0.057 seconds)
0: jdbc:hive2://localhost:10000/default> USE remote_DB;
No rows affected (0.022 seconds)
0: jdbc:hive2://localhost:10000/default> show tables;
+----------------------------+
| tab_name |
+----------------------------+
| passwd_ext_nonpart |
| passwd_ext_nonpart_remote |
| passwd_int_nonpart |
| passwd_int_part |
| passwd_int_trans |
+----------------------------+
5 rows selected (0.041 seconds)
0: jdbc:hive2://localhost:10000/default> USE local_DB;
No rows affected (0.022 seconds)
0: jdbc:hive2://localhost:10000/default> show tables;
+---------------------------+
| tab_name |
+---------------------------+
| passwd_ext_nonpart |
| passwd_ext_nonpart_local |
| passwd_int_nonpart |
| passwd_int_part |
| passwd_int_trans |
+---------------------------+
5 rows selected (0.036 seconds)
0: jdbc:hive2://localhost:10000/default>
0: jdbc:hive2://localhost:10000/default> !q
Closing: 0: jdbc:hive2://localhost:10000/default;
++ sudo -u hdfs hdfs dfs -ls /user/hive/
Found 4 items
drwxr-xr-x - hive Administrators 0 2021-06-03 17:11 /user/hive/.hiveJars
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 /user/hive/local_DB
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 /user/hive/remote_DB
drwxrwxrwt - root Administrators 0 2021-06-03 17:10 /user/hive/warehouse
++ sudo -u hdfs hdfs dfs -ls /user/hive/local_DB
Found 4 items
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 /user/hive/local_DB/passwd_ext_nonpart_local
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 /user/hive/local_DB/passwd_int_nonpart
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 /user/hive/local_DB/passwd_int_part
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 /user/hive/local_DB/passwd_int_trans
++ sudo -u hdfs hdfs dfs -ls -R /user/hive/local_DB/passwd_ext_nonpart_local
-rwxrwxrwx 3 hive Administrators 2224 2021-06-03 17:25 /user/hive/local_DB/passwd_ext_nonpart_local/passwd
++ sudo -u hdfs hdfs dfs -ls hdfs://10.1.1.15:8020/user/hive/
Found 4 items
drwxr-xr-x - hive Administrators 0 2021-06-03 17:11 hdfs://10.1.1.15:8020/user/hive/.hiveJars
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 hdfs://10.1.1.15:8020/user/hive/local_DB
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 hdfs://10.1.1.15:8020/user/hive/remote_DB
drwxrwxrwt - root Administrators 0 2021-06-03 17:10 hdfs://10.1.1.15:8020/user/hive/warehouse
++ sudo -u hdfs hdfs dfs -ls hdfs://10.1.1.15:8020/user/hive/remote_DB
Found 4 items
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 hdfs://10.1.1.15:8020/user/hive/remote_DB/passwd_ext_nonpart_remote
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 hdfs://10.1.1.15:8020/user/hive/remote_DB/passwd_int_nonpart
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 hdfs://10.1.1.15:8020/user/hive/remote_DB/passwd_int_part
drwxrwxrwx - hive Administrators 0 2021-06-03 17:25 hdfs://10.1.1.15:8020/user/hive/remote_DB/passwd_int_trans
++ sudo -u hdfs hdfs dfs -ls -R hdfs://10.1.1.15:8020/user/hive/remote_DB/passwd_ext_nonpart_remote
-rwxrwxrwx 3 hive Administrators 2224 2021-06-03 17:25 hdfs://10.1.1.15:8020/user/hive/remote_DB/passwd_ext_nonpart_remote/passwd
++ true
]0;hadoop@ip-10-15-1-140:~/scripts
[hadoop@ip-10-15-1-140 scripts]$ exit
exit
Script done on 2021-06-03 17:27:51+0000