Cloudera Docs

Creating and loading a managed, partitioned table

You use the PARTITIONED BY clause to create a partitioned table. To insert data into particular partitions in the table, you use a familiar ANSI SQL statement that indentifies the data for each partition. A simple example shows you have to accomplish this basic task.

  1. Create a partitioned table.
    Hive and Impala example: 
    CREATE TABLE IF NOT EXISTS pageviews (userid VARCHAR(64), link STRING, origin STRING) PARTITIONED BY (datestamp STRING);
  2. Insert data into the table.
    Assign null values to columns you do not want to assign a value.
    Hive example: 
    INSERT INTO TABLE pageviews PARTITION (datestamp = '2022-09-23') VALUES ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null);
    Impala example: 
    INSERT INTO TABLE pageviews PARTITION (datestamp = '2022-09-23') VALUES (CAST ('jsmith' AS VARCHAR(64)), 'mail.com', 'sports.com'), (CAST ('jdoe' AS VARCHAR(64)), 'mail.com', null);
    Cast strings to VARCHAR(64) when you insert data of type STRING into the Impala table.
  3. Check that the 2022-09-23 partition was created.
    Hive and Impala example: 
    SHOW PARTITIONS pageviews;
    Hive output looks something like this:
    +-----------------------+
|       partition       |
+-----------------------+
| datestamp=2022-09-23  |
+-----------------------+
    Impala output looks something like this:
    +------------+-------+--------+------+--------------+-------------------+---------+-------------------+----------------------------------------------------------------------------------------------------------------+
| datestamp  | #Rows | #Files | Size | Bytes Cached | Cache Replication | Format  | Incremental stats | Location                                                                                                       |
+------------+-------+--------+------+--------------+-------------------+---------+-------------------+----------------------------------------------------------------------------------------------------------------+
| 2022-09-23 | -1    | 1      | 938B | NOT CACHED   | NOT CACHED        | PARQUET | false             | s3a://cdpsaasdemo-cdp-private-default-1ogvplm/warehouse/tablespace/managed/hive/pageviews/datestamp=2022-09-23 |
| Total      | -1    | 1      | 938B | 0B           |                   |         |                   |                                                                                                                |
+------------+-------+--------+------+--------------+-------------------+---------+-------------------+----------------------------------------------------------------------------------------------------------------+