Vithal Sampagar vithalsamp
vithalsamp
/ Hive-like-statement-example-escape.sql
Created
February 25, 2018 10:18
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
| hive> select * from stud where name LIKE '%A\%%'; | |
| OK | |
| 12 200 AA% | |
| Time taken: 0.061 seconds, Fetched: 1 row(s) | |
| hive> |
vithalsamp
/ Hive-like-statement-example1.sql
Last active
February 25, 2018 10: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
| SELECT * FROM table WHERE col1 LIKE 'ab%; | |
| SELECT * FROM table WHERE col1 NOT LIKE 'ab%; | |
| You can combine Hive LIKE operator in multiple conditions: | |
| SELECT * FROM table WHERE LOWER(col1) LIKE 'ab% or LOWER(col1) NOT LIKE 'cd%; |
vithalsamp
/ Hive-like-statement-example.sql
Created
February 25, 2018 10: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
| hive> select 'abc' LIKE 'abc'; | |
| OK | |
| true | |
| Time taken: 0.084 seconds, Fetched: 1 row(s) | |
| hive> select 'abc' LIKE 'a%' ; | |
| OK | |
| true | |
| Time taken: 0.087 seconds, Fetched: 1 row(s) |
vithalsamp
/ Hive-load-fixed-width-file4.sql
Created
February 25, 2018 10:09
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
| hive> select substr(text_col,1,5) as userid, substr(text_col,6,2) as sl,substr(text_col,8,8) as date_col,substr(text_col,16,10) as ID,substr(text_col,26,7) as key_col from single_col_stg; | |
| OK | |
| 16524 01 10172012 3930621977 TXNPUES | |
| 19167 01 01142013 2368183100 OUNHQEX | |
| 19166 01 01142013 3714468136 GHAKASC | |
| 19167 01 01142013 2632703881 PAHFSAP | |
| 80495 01 11212012 2766389794 XDZANTV | |
| 80507 01 11212012 4609266335 BWWYEZL | |
| 80509 01 11212012 1092717420 QJYPKVO | |
| … |
vithalsamp
/ Hive-load-fixed-width-file3.sql
Created
February 25, 2018 10:08
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
| hive> LOAD DATA LOCAL INPATH './FixedWidthExample.txt' OVERWRITE INTO TABLE single_col_stg; | |
| Loading data to table default.single_col_stg | |
| Table default.single_col_stg stats: [numFiles=1, numRows=0, totalSize=560, rawDataSize=0] | |
| OK | |
| Time taken: 0.847 seconds | |
| hive> |
vithalsamp
/ Hive-load-fixed-width-file-load-view-data.sql
Created
February 25, 2018 10:07
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
| hive> select * from fixed_w_table; | |
| OK | |
| 16524 01 10172012 3930621977 TXNPUES | |
| 19167 01 01142013 2368183100 OUNHQEX | |
| 19166 01 01142013 3714468136 GHAKASC | |
| 19167 01 01142013 2632703881 PAHFSAP | |
| 80495 01 11212012 2766389794 XDZANTV | |
| 80507 01 11212012 4609266335 BWWYEZL | |
| 80509 01 11212012 1092717420 QJYPKVO | |
| 80497 01 11212012 3386366766 SOQLCMU |
vithalsamp
/ Hive-load-fixed-width-file.sql
Created
February 25, 2018 10:06
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 fixed_w_table | |
| (userid STRING, | |
| sl STRING, | |
| date_col string, | |
| ID string, | |
| key_col string | |
| ) | |
| ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' WITH SERDEPROPERTIES ("input.regex" = "(.{5})(.{2})(.{8})(.{10})(.{7}).*" ) | |
| LOCATION '/data/fw'; |
vithalsamp
/ Apache-Hive-Data.sql
Created
February 25, 2018 10:02
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 TABLE HIVE_TABLE_NAME ( | |
| TINYINT_COLUMN TINYINT, | |
| SMALLINT_COLUMN SMALLINT, | |
| INTEGER_COLUMN INT, | |
| BIGINT_COLUMN BIGINT, | |
| BOOLEAN_COLUMN BOOLEAN, | |
| DOUBLE_COLUMN DOUBLE, | |
| NUMERIC_COLUMN NUMERIC, | |
| DECIMAL_COLUMN DECIMAL, | |
| DATE_COLUMN DATE, |
vithalsamp
/ Hadoop_unarchive.txt
Created
February 25, 2018 09:36
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
| hive> ALTER TABLE HAR_test UNARCHIVE PARTITION (deptID = 100, deptID = 101, deptID = 102, deptID = 103); | |
| Copying har://hdfs-quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103/data.har to hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103/.hive-staging_hive_2018-01-24_06-20-59_561_3258031086519991838-1/-ext-10000 | |
| Succefully Copied har://hdfs-quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103/data.har to hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103/.hive-staging_hive_2018-01-24_06-20-59_561_3258031086519991838-1/-ext-10000 | |
| Moving hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103/.hive-staging_hive_2018-01-24_06-20-59_561_3258031086519991838-1/-ext-10000 to hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103_INTERMEDIATE_EXTRACTED | |
| Moving hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103 to hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103_INTERMEDIATE_A |
vithalsamp
/ Hadoop_archive1.txt
Created
February 25, 2018 09:35
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
| ALTER TABLE table_name ARCHIVE PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, …); | |
| hive> ALTER TABLE HAR_test ARCHIVE PARTITION (deptID = 100, deptID = 101, deptID = 102, deptID = 103); | |
| intermediate.archived is hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103_INTERMEDIATE_ARCHIVED | |
| intermediate.original is hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103_INTERMEDIATE_ORIGINAL | |
| Creating data.har for hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103 | |
| in hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103/.hive-staging_hive_2018-01-24_06-13-57_086_6776808276619448575-1/-ext-10000/partlevel | |
| Please wait... (this may take a while) | |
| Moving hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103/.hive-staging_hive_2018-01-24_06-13-57_086_6776808276619448575-1/-ext-10000/partlevel to hdfs://quickstart.cloudera:8020/user/hive/warehouse/har_test/deptid=103_INTERMEDIATE_ARCHIVED | |
| Moving |