hive Create Database and Table Statement Create Table

Example

Creating a managed table with partition and stored as a sequence file. The data format in the files is assumed to be field-delimited by Ctrl-A (^A) and row-delimited by newline. The below table is created in hive warehouse directory specified in value for the key hive.metastore.warehouse.dir in the Hive config file hive-site.xml.

CREATE TABLE view
(time INT, 
id BIGINT,
url STRING, 
referrer_url STRING,
add STRING COMMENT 'IP of the User')
COMMENT 'This is view table'
PARTITIONED BY(date STRING, region STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
STORED AS SEQUENCEFILE;

Creating a external table with partitions and stored as a sequence file. The data format in the files is assumed to be field-delimited by ctrl-A and row-delimited by newline. The below table is created in the location specified and it comes handy when we already have data. One of the advantages of using an external table is that we can drop the table without deleting the data. For instance, if we create a table and realize that the schema is wrong, we can safely drop the table and recreate with the new schema without worrying about the data.Other advantage is that if we are using other tools like pig on same files, we can continue using them even after we delete the table.

CREATE EXTERNAL TABLE view
(time INT, 
id BIGINT,
url STRING, 
referrer_url STRING,
add STRING COMMENT 'IP of the User')
COMMENT 'This is view table'
PARTITIONED BY(date STRING, region STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
STORED AS SEQUENCEFILE
LOCATION '<hdfs_location>';

Creating a table using select query and populating results from query, these statements are known as CTAS(Create Table As Select).

There are two parts in CTAS, the SELECT part can be any SELECT statement supported by HiveQL. The CREATE part of the CTAS takes the resulting schema from the SELECT part and creates the target table with other table properties such as the SerDe and storage format.

CTAS has these restrictions:

• The target table cannot be a partitioned table.
• The target table cannot be an external table.
• The target table cannot be a list bucketing table.

CREATE TABLE new_key_value_store
ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"
STORED AS RCFile
AS
SELECT * FROM page_view
SORT BY url, add;

Create Table Like:

The LIKE form of CREATE TABLE allows you to copy an existing table definition exactly (without copying its data). In contrast to CTAS, the statement below creates a new table whose definition exactly matches the existing table in all particulars other than table name. The new table contains no rows.

CREATE TABLE empty_page_views
LIKE page_views;