access_time 12 months ago languageEnglish
more_vert

Create Table Stored as CSV, TSV, JSON Format - Hive SQL

visibility 3,590 comment 0

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 12 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

Apache Hive 3.1.2 Installation on Linux Guide
visibility 2671
thumb_up 1
access_time 8 months ago
visibility 176
thumb_up 0
access_time 5 months ago
visibility 1044
thumb_up 0
access_time 11 months ago