Create Table Stored as CSV, TSV, JSON Format - Hive SQL
insights Stats
warning Please login first to view stats information.
Raymond
Hadoop, Hive & HBase
Articles about Apache Hadoop, Hive and HBase installation, performance tuning and general tutorials.
*The yellow elephant logo is a registered trademark of Apache Hadoop.
This page shows how to create Hive tables with storage file format as CSV or TSV via Hive SQL (HQL).
Create table stored as CSV
Example:
CREATE TABLE IF NOT EXISTS hql.customer_csv(cust_id INT, name STRING, created_date DATE) COMMENT 'A table to store customer records.' ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( "separatorChar" = ",", "quoteChar" = "'", "escapeChar" = "\\" );
Create table stored as TSV
Example:
CREATE TABLE IF NOT EXISTS hql.customer_tsv(cust_id INT, name STRING, created_date DATE) COMMENT 'A table to store customer records.' ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( "separatorChar" = "\t", "quoteChar" = "'", "escapeChar" = "\\" );
infoIf text file delimiter, escape or quote characters are different, please change accordingly.
Create table stored as JSON
Example:
CREATE TABLE IF NOT EXISTS hql.customer_json(cust_id INT, name STRING, created_date DATE) COMMENT 'A table to store customer records.' ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' STORED AS TEXTFILE;
Install Hive database
Follow the article below to install Hive on Windows 10 via WSL if you don't have available available Hive database to practice Hive SQL:
Examples on this page are based on Hive 3.* syntax.
Run query
All these SQL statements can be run using beeline CLI:
$HIVE_HOME/bin/beeline --silent=true
The above command line connects to the default HiveServer2 service via beeline. Once beeline is loaded, type the following command to connect:
0: jdbc:hive2://localhost:10000> !connect jdbc:hive2://localhost:10000 Enter username for jdbc:hive2://localhost:10000: hive Enter password for jdbc:hive2://localhost:10000: 1: jdbc:hive2://localhost:10000>
The terminal looks like the following screenshot:
info Last modified by Administrator 5 years ago
copyright
This page is subject to Site terms.
comment Comments
No comments yet.