Posts Tagged ‘hive’

Hive: Make CLI output files comma delimited

bash >> hive -e ‘select * from some_Table’ | sed ‘s/[\t]/,/g’ > outputfile.txt

Here [\t] means Control+V and then the tab button, i.e.
sed ‘s//,/g’

Example:

[user@server]$ hive -e "use dbname ; select * from tablename" | sed ‘s/ /,/g’ > kpi_event_jan8.csv

Hive: Get Column Names in CLI Queries

Add this to your query:

set hive.cli.print.header=true;

Example:

hive -e "set hive.cli.print.header=true; use a_db; select * from a_table;" > test

hive_logo_medium

HiveQL: SQL Connector

 
To bridge the difference between SQL and HiveQL, we have the SQL Connector
feature to translate standard SQL-92 queries into equivalent HiveQL queries. The SQL
Connector performs syntactical translations and structural transformations. For
example:
1. Quoted Identifiers
HiveQL uses back-quote while SQL uses double quote when quoting identifiers.
Even when a driver reports the back-quote as the quote character, some
applications still generate double quoted identifiers.
2. Table Aliases
HiveQL does not support the AS keyword between a table reference and its
alias.
3. JOIN, INNER JOIN and CROSS JOIN
SQL INNER JOIN and CROSS JOIN syntax is translated to HiveQL JOIN syntax.
4. TOP N/LIMIT
SQL TOP N queries are transformed to HiveQL LIMIT queries.

Quick-Tip: Cross-DB Hive Joins in 1 Line

select * from db.table f, db2.table2 j WHERE f.id = j.id;

Quick Tip: Query Hive on a Bash CLI and Write Results to a File in 1 Line

 hive -S -e “SELECT * FROM your_table” > ~/outfile.tsv