Create a table in Hive

You can create, modify, update, and remove tables in Hive using beeline or any other tool to access Hive.

  1. Enter the beeline command shell by beeline command in your cluster:
    ~ beelinex
  2. Enter the database you want to access.
    ~ use <DATABASE_NAME>;
    Or create and use a new database. In this following example, abfsdb is the name of the database.
    create database abfsdb;
    ~ use abfsdb;
  3. Create a table inside the container in a directory named table_1. If the directory does not exist, it is automatically created:
    ~ create external table myTable(key STRING, value INT) location 
    'abfs://abfscontainer@abfstorageacc.dfs.core.windows.net/table_1/';
  4. View the table structure using the show command:
    ~ show create table myTable;
    INFO  : Compiling command(queryId=hive_20200520153116_301af680-9630-49d1-af40-3dfa5349e52a): show create table myTable
    INFO  : Semantic Analysis Completed (retrial = false)
    INFO  : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:createtab_stmt, type:string, comment:from deserializer)], properties:null)
    INFO  : Completed compiling command(queryId=hive_20200520153116_301af680-9630-49d1-af40-3dfa5349e52a); Time taken: 0.052 seconds
    INFO  : Executing command(queryId=hive_20200520153116_301af680-9630-49d1-af40-3dfa5349e52a): show create table myTable
    INFO  : Starting task [Stage-0:DDL] in serial mode
    INFO  : Completed executing command(queryId=hive_20200520153116_301af680-9630-49d1-af40-3dfa5349e52a); Time taken: 0.072 seconds
    INFO  : OK
    
    +----------------------------------------------------+
    |                   createtab_stmt                   |
    +----------------------------------------------------+
    | CREATE EXTERNAL TABLE `myTable`(                   |
    |   `key` string,                                    |
    |   `value` int)                                     |
    | ROW FORMAT SERDE                                   |
    |   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  |
    | STORED AS INPUTFORMAT                              |
    |   'org.apache.hadoop.mapred.TextInputFormat'       |
    | OUTPUTFORMAT                                       |
    |   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
    | LOCATION                                           |
    |   'abfs://abfscontainer@abfstorageacc.dfs.core.windows.net/table_1' |
    | TBLPROPERTIES (                                    |
    |   'bucketing_version'='2',                         |
    |   'transient_lastDdlTime'='1589988617')            |
    +----------------------------------------------------+
    14 rows selected (0.162 seconds)

After the table is created inside the Azure storage, it behaves like a regular table and you can use all the hql commands. For example, to insert values in the table, run ~ insert into myTable values("myKey", 1);.