Vithal Sampagar vithalsamp
vithalsamp
/ Beeline Hive Commands.sql
Created
February 25, 2018 11:53
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 0: jdbc:hive2://192.168.0.41:10000/test_db> select * from t1; | |
| +-------+--------+--+ | |
| | t1.x | t1.s | | |
| +-------+--------+--+ | |
| | 1 | one | | |
| | 3 | three | | |
| | 2 | two | | |
| | 1 | one | | |
| | 1 | four | | |
| | 3 | five | |
vithalsamp
/ Hive vs beeline1.sh
Created
February 25, 2018 11:49
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| beeline> !connect jdbc:hive2:// | |
| Connecting to jdbc:hive2:// | |
| Enter username for jdbc:hive2://: username | |
| Enter password for jdbc:hive2://: ******* | |
| 18/01/03 10:51:03 [main]: WARN service.CompositeService: Unable to create operation log root directory: /tmp/hive/operation_logs | |
| Connected to: Apache Hive (version 1.2.1000.2.5.0.0-1245) | |
| Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245) | |
| Transaction isolation: TRANSACTION_REPEATABLE_READ | |
| 0: jdbc:hive2://> |
vithalsamp
/ Hive vs beeline.sh
Created
February 25, 2018 11:48
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $ beeline -u jdbc:hive2:// | |
| Connecting to jdbc:hive2:// | |
| 18/01/03 10:47:45 [main]: WARN service.CompositeService: Unable to create operation log root directory: /tmp/hive/operation_logs | |
| Connected to: Apache Hive (version 1.2.1000.2.5.0.0-1245) | |
| Driver: Hive JDBC (version 1.2.1000.2.5.0.0-1245) | |
| Transaction isolation: TRANSACTION_REPEATABLE_READ | |
| Beeline version 1.2.1000.2.5.0.0-1245 by Apache Hive | |
| 0: jdbc:hive2://> | |
| 0: jdbc:hive2://> show tables; | |
| OK |
vithalsamp
/ Impala create external table using Like.sql
Created
February 25, 2018 11:43
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE EXTERNAL TABLE IF NOT EXISTS stud_like_ext | |
| LIKE stud_int | |
| LOCATION '/data/stud_list'; | |
| Query: select * from stud_like_ext | |
| +---------+---------+-------+ | |
| | stud_id | dept_id | name | | |
| +---------+---------+-------+ | |
| | 101 | 201 | 'AAA' | | |
| | 102 | 202 | 'BBB' | |
vithalsamp
/ Impala create external table ex.sql
Created
February 25, 2018 11:40
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| create EXTERNAL TABLE IF NOT EXISTS stud_ext | |
| (STUD_ID int, | |
| DEPT_ID int, | |
| NAME string | |
| ) | |
| ROW FORMAT DELIMITED | |
| FIELDS TERMINATED BY ',' | |
| STORED AS TEXTFILE | |
| LOCATION '/data/stud_list'; |
vithalsamp
/ Netezza external Tables.sql
Created
February 25, 2018 11:28
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| TRAINING.ADMIN(ADMIN)=> insert into stud_dtls SELECT * FROM EXTERNAL '/home/hduser/stud_detls1.csv' ( id int, dept_id int, name varchar(20) ) USING ( DELIMITER ',' ); | |
| INSERT 0 6 | |
| TRAINING.ADMIN(ADMIN)=> |
vithalsamp
/ netezza nzload.sh
Created
February 25, 2018 11:27
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| hduser@vithal-Inspiron-3558:~$ nzload -host 192.168.0.100 -u admin -pw password -db training -df /home/hduser/stud_detls1.csv -t stud_dtls -delim ',' | |
| Load session of table 'STUD_DTLS' completed successfully | |
| hduser@vithal-Inspiron-3558:~$ |
vithalsamp
/ Hadoop hdfs copy to flat file.sh
Created
February 25, 2018 11:20
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| hduser@vithal-Inspiron-3558:~$ hdfs dfs -copyToLocal /data/stud_dtls/stud_detls.csv /home/hduser/stud_detls1.csv | |
| hduser@vithal-Inspiron-3558:~$ ls -ltr /home/hduser/stud_detls1.csv | |
| -rw-r--r-- 1 hduser hadoop 72 Jan 16 18:21 /home/hduser/stud_detls1.csv | |
| hduser@vithal-Inspiron-3558:~$ |
vithalsamp
/ load hdfs file to Netezza1.sh
Created
February 25, 2018 11:17
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| hduser@vithal-Inspiron-3558:~$ hdfs dfs -cat /data/stud_dtls/stud_detls.csv | |
| 101,100,AAA | |
| 102,101,BBB | |
| 103,102,CCC | |
| 104,100,DDD | |
| 105,102,EEE | |
| 106,103,FFF | |
| hduser@vithal-Inspiron-3558:~$ | |
| hduser@vithal-Inspiron-3558:~$ hdfs dfs -cat /data/stud_dtls/stud_detls.csv | nzload -host 192.168.0.100 -u admin -pw password -db training -t stud_dtls -delim ',' |
vithalsamp
/ load hdfs file to Netezza using nzload.sh
Created
February 25, 2018 11:16
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| hduser@vithal-Inspiron-3558:~$ nzload -host 192.168.0.100 -u admin -pw password -db training -df 'hdfs:///data/stud_dtls/stud_detls.csv' -t stud_dtls -delim ',' | |
| Error: Specified value 'hdfs:///data/stud_dtls/stud_detls.csv' in option Datafile does not exists. | |
| Usage: nzload [-h|-rev] [<options>] | |
| Try using 'nzload -h' for more information. | |
| hduser@vithal-Inspiron-3558:~$ |