Create Table as SELECT - Hive SQL

access_time 4 months ago visibility92 comment 0

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

Create table as select

Example:

CREATE TABLE IF NOT EXISTS hql.transactions_copy
STORED AS PARQUET
AS 
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. 

Example:

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 at 4 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

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

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

local_offer hadoop local_offer hive local_offer WSL local_offer big-data-on-wsl

visibility 5009
thumb_up 0
access_time 2 years ago

Previously, I demonstrated how to configured Apache Hive 3.0.0 on Windows 10. Apache Hive 3.0.0 Installation on Windows 10 Step by Step Guide On this page, I’m going to show you how to install the latest version Apache Hive 3.1.1 on Windows 10 using Windows Subsystem for Linux (WSL) Ubuntu ...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 43
thumb_up 0
access_time 3 months ago

In SQL Server, ISNULL function is commonly used to populate a value for null columns.  In Teradata, there is no ISNULL function but COALESCE and CASE WHEN can be used as alternatives.  SELECT COALESCE(NULL,'ABC','CDE'); Result: ABC SELECT COALESCE(Col1, Col2,'DEFAULT') FROM ...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 153
thumb_up 0
access_time 3 months ago

Teradata LPAD function is used to add repeated characters at the beginning of a string to increase the string to a specified length. It can be used to add leading space or zeros to a string. LPAD(source_string, length, fill_string) Returns the source_string padded to the left with the ...

About column

Articles about Apache Hadoop installation, performance tuning and general tutorials.

*The yellow elephant logo is a registered trademark of Apache Hadoop.

rss_feed Subscribe RSS