Create Table as SELECT - Hive SQL

visibility 5,609 access_time 2 years ago languageEnglish

This page shows how to create a managed(internal) Hive table from a SELECT statement via Hive SQL (HQL).

Create table as select


CREATE TABLE IF NOT EXISTS hql.transactions_copy
SELECT * FROM hql.transactions;

A MapReduce job will be submitted to create the table from SELECT statement.

Create table like

CREATE TABLE LIKE statement will create an empty table as the same schema of the source table. 


CREATE TABLE IF NOT EXISTS hql.transactions_empty
LIKE hql.transactions;

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 2 years ago copyright This page is subject to Site terms.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

timeline Stats
Page index 8.85
More from Kontext
Configure a SQL Server Database as Remote Hive Metastore
visibility 5,481
thumb_up 0
access_time 2 years ago
Teradata SQL - First Day of a Month
visibility 904
thumb_up 0
access_time 2 years ago