如何在Hive中基于已有表结构创建带指定分区的新表？

Absolutely feasible! The problem with your initial statement is that Hive doesn’t support combining LIKE with PARTITIONED BY in one CREATE TABLE call. But there are two solid, generic workarounds that let you replicate the original table’s schema while adding a partition—no manual schema parsing or hardcoding required.

Option 1: Use CTAS (Create Table As Select) with LIMIT 0 (No Data Load) #

This method copies the original table’s structure directly, and lets you define the partition column in the same statement. The key is to handle the partition column correctly depending on whether it already exists in the source table.

If the partition column doesn’t exist in the original table: #

CREATE TABLE IF NOT EXISTS new_table
PARTITIONED BY (col1 int)
AS
SELECT * FROM old_table LIMIT 0;

The LIMIT 0 ensures no data is copied—we only replicate the schema of the original table plus the new partition column.

If the partition column already exists in the original table: #

We need to dynamically select all columns except the partition column to avoid duplication. Use Hive’s metadata to fetch the column list automatically:

-- Set variables for easy reuse
SET hivevar:source_table=old_table;
SET hivevar:partition_col=col1;
SET hivevar:partition_type=int;

-- Fetch all columns except the partition column from metadata
SET hivevar:non_partition_cols=(
  SELECT concat_ws(',', collect_list(column_name))
  FROM information_schema.columns
  WHERE table_name = '${source_table}'
    AND column_name != '${partition_col}'
);

-- Create the partitioned table
CREATE TABLE IF NOT EXISTS new_table
PARTITIONED BY (${partition_col} ${partition_type})
AS
SELECT ${non_partition_cols} FROM ${source_table} LIMIT 0;

This works if your Hive setup has access to information_schema (most modern distributions do). If not, you can parse the output of DESCRIBE ${source_table} in a shell script to build the column list dynamically.

Option 2: Reuse the Original Table’s Create Statement #

Another approach is to fetch the original table’s CREATE TABLE statement, modify it to add the partition clause, and execute the modified statement.

Using Hive CLI/Script: #

-- Fetch the original create statement into a variable
SET hivevar:create_stmt=(SHOW CREATE TABLE old_table);

-- Modify the statement: replace the table name and add the partition clause
SET hivevar:new_create_stmt=regexp_replace(${create_stmt}, 'CREATE TABLE.*old_table', 'CREATE TABLE IF NOT EXISTS new_table PARTITIONED BY (col1 int)');

-- Execute the modified statement
${new_create_stmt};

You might need to tweak the regex slightly depending on the exact format of your original CREATE TABLE output, but this avoids hardcoding the schema entirely.

Quick Notes: #

• Both methods preserve the original table’s data types, comments, and storage properties (like SerDe settings, file format).
• If you need to copy data later, use INSERT INTO new_table PARTITION (col1) SELECT * FROM old_table; (adjust the SELECT clause if you excluded the partition column in Option 1).

内容的提问来源于stack exchange，提问作者Justin