Create Table Stored as CSV, TSV, JSON Format - Hive SQL
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" = "\\" );
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: