Saturday, November 22, 2014

SQL to Hive Cheat Sheet

Retrieving Information

FunctionMySQLHive
Retrieving Information (General)SELECT from_columns FROM table WHERE conditions;SELECT from_columns FROM table WHERE conditions;
Retrieving All ValuesSELECT * FROM table;SELECT * FROM table;
Retrieving Some ValuesSELECT * FROM table WHERE rec_name = "value";SELECT * FROM table WHERE rec_name = "value";
Retrieving With Multiple CriteriaSELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";
Retrieving Specific ColumnsSELECT column_name FROM table;SELECT column_name FROM table;
Retrieving Unique OutputSELECT DISTINCT column_name FROM table;SELECT DISTINCT column_name FROM table;
SortingSELECT col1, col2 FROM table ORDER BY col2;SELECT col1, col2 FROM table ORDER BY col2;
Sorting ReverseSELECT col1, col2 FROM table ORDER BY col2 DESC;SELECT col1, col2 FROM table ORDER BY col2 DESC;
Counting RowsSELECT COUNT(*) FROM table;SELECT COUNT(*) FROM table;
Grouping With CountingSELECT owner, COUNT(*) FROM table GROUP BY owner;SELECT owner, COUNT(*) FROM table GROUP BY owner;
Maximum ValueSELECT MAX(col_name) AS label FROM table;SELECT MAX(col_name) AS label FROM table;
Selecting from multiple tables (Join same table using alias w/”AS”)SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;SELECT pet.name, comment FROM pet JOIN event ON (pet.name = event.name)

Metadata

FunctionMySQLHive
Selecting a databaseUSE database;USE database;
Listing databasesSHOW DATABASES;SHOW DATABASES;
Listing tables in a databaseSHOW TABLES;SHOW TABLES;
Describing the format of a tableDESCRIBE table;DESCRIBE (FORMATTED|EXTENDED) table;
Creating a databaseCREATE DATABASE db_name;CREATE DATABASE db_name;
Dropping a databaseDROP DATABASE db_name;DROP DATABASE db_name (CASCADE);

Current SQL Compatibility

Command Line

FunctionHive
Run Queryhive -e 'select a.col from tab1 a'
Run Query Silent Modehive -S -e 'select a.col from tab1 a'
Set Hive Config Variableshive -e 'select a.col from tab1 a' -hiveconf hive.root.logger=DEBUG,console
Use Initialization Scripthive -i initialize.sql
Run Non-Interactive Script

Source
hive -f script.sql

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.