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"    = "\\"
);

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:

• Apache Hive 3.1.1 Installation on Windows 10 using Windows Subsystem for Linux

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: