Menu
I can do
ALTER TABLE table_name ADD COLUMNS (user_id BIGINT)
to add a new column to the end of my non-partition columns and before my partition columns.
Is there any way to add a new column to anywhere among my non-partition columns? For example, I would like to put this new column user_id as the first column of my table
949
Yes, we can add column inside a table in Hive using a command: ALTER TABLE table_name ADD COLUMNS (column _name datatypes);
The easiest way to select specific columns in the Hive query is by specifying the column name in the select statement. SELECT col1, col3, col4 ....
ALTER TABLE default. test_table ADD columns (column1 string,column2 string) CASCADE; From the Hive documentation: “ALTER TABLE CHANGE COLUMN with CASCADE command changes the columns of a table's metadata, and cascades the same change to all the partition metadata.
This command moves column_name after column_name2: alter table table_name change column column_name column_name column_name_type after column_name2; You have to put the column_name twice (or you can change column name) and type of the column.
Yes it is possible to change the location of columns but only after adding it in the table using CHANGE COLUMN
In your case, first add the column user_id to the table with below command:
ALTER TABLE table_name ADD COLUMNS (user_id BIGINT);
Now to make user_id column as the first column in your table use change column with FIRST clause:
ALTER TABLE table_name CHANGE COLUMN user_id user_id BIGINT first;
This will move the user_id column to the first position.
Similarly you can use After instead of first if you want to move the specified column after any other column. Like say, I want to move dob column after user_id column. Then my command would be:
ALTER TABLE table_name CHANGE COLUMN dob dob date AFTER user_id;
Please note that this commands changes metadata only. If you are moving columns, the data must already match the new schema or you must change it to match by some other means.
75
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
