Repository: incubator-carbondata Updated Branches: refs/heads/master 8b4eec82b -> ca07119d1
added alter table documentation Project: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/commit/ed922ee1 Tree: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/tree/ed922ee1 Diff: http://git-wip-us.apache.org/repos/asf/incubator-carbondata/diff/ed922ee1 Branch: refs/heads/master Commit: ed922ee1d7bfa862923ec31832260ef6bab32ae3 Parents: 8b4eec8 Author: PallaviSingh1992 <pallavisingh_1...@yahoo.co.in> Authored: Thu Apr 13 17:09:04 2017 +0530 Committer: chenliang613 <chenliang...@huawei.com> Committed: Wed Apr 19 14:46:51 2017 +0800 ---------------------------------------------------------------------- docs/ddl-operation-on-carbondata.md | 209 ++++++++++++++++++++++++++----- 1 file changed, 176 insertions(+), 33 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-carbondata/blob/ed922ee1/docs/ddl-operation-on-carbondata.md ---------------------------------------------------------------------- diff --git a/docs/ddl-operation-on-carbondata.md b/docs/ddl-operation-on-carbondata.md index d9d5718..2983b8a 100644 --- a/docs/ddl-operation-on-carbondata.md +++ b/docs/ddl-operation-on-carbondata.md @@ -25,10 +25,15 @@ The following DDL operations are supported in CarbonData : * [CREATE TABLE](#create-table) * [SHOW TABLE](#show-table) +* [ALTER TABLE](#alter-table) + - [RENAME TABLE](#rename-table) + - [ADD COLUMN](#add-column) + - [DROP COLUMNS](#drop-columns) + - [CHANGE DATA TYPE](#change-data-type) * [DROP TABLE](#drop-table) * [COMPACTION](#compaction) * [BUCKETING](#bucketing) -* [TABLE RENAME](#table-rename) + ## CREATE TABLE This command can be used to create a CarbonData table by specifying the list of fields along with the table properties. @@ -133,6 +138,172 @@ TBLPROPERTIES ('COLUMN_GROUPS'='(column1, column2), SHOW TABLES IN ProductSchema; ``` +## ALTER TABLE + +The following section shall discuss the commands to modify the physical or logical state of the existing table(s). + +### **RENAME TABLE** + +This command is used to rename the existing table. +``` + ALTER TABLE [db_name.]table_name RENAME TO new_table_name; +``` + +#### Parameter Description +| Parameter | Description | +|---------------|-----------------------------------------------------------------------------------------------| +| db_Name | Name of the database. If this parameter is left unspecified, the current database is selected.| +|table_name | Name of the existing table. | +|new_table_name | New table name for the existing table. | + +#### Usage Guidelines + +- Queries that require the formation of path using the table name for reading carbon store files, running in parallel with Rename command might fail during the renaming operation. + +- Renaming of Secondary index table(s) is not permitted. + +#### Examples: + +``` + ALTER TABLE carbon RENAME TO carbondata; +``` + +``` + ALTER TABLE test_db.carbon RENAME TO test_db.carbondata; +``` + +### **ADD COLUMN** + +This command is used to add a new column to the existing table. + +``` + ALTER TABLE [db_name.]table_name ADD COLUMNS (col_name data_type,...) + TBLPROPERTIES('DICTIONARY_INCLUDE'='col_name,...', + 'DICTIONARY_EXCLUDE'='col_name,...', + 'DEFAULT.VALUE.COLUMN_NAME'='default_value'); +``` + +#### Parameter Description +| Parameter | Description | +|--------------------|-----------------------------------------------------------------------------------------------------------| +| db_Name | Name of the database. If this parameter is left unspecified, the current database is selected. | +| table_name | Name of the existing table. | +| col_name data_type | Name of comma-separated column with data type. Column names contain letters, digits, and underscores (_). | + +NOTE: Do not name the column after name, tupleId, PositionId, and PositionReference when creating Carbon tables because they are used internally by UPDATE, DELETE, and secondary index. + +#### Usage Guidelines + +- Apart from DICTIONARY_INCLUDE, DICTIONARY_EXCLUDE and default_value no other property will be read. If any other property name is specified, error will not be thrown, it will be ignored. + +- If default value is not specified, then NULL will be considered as the default value for the column. + +- For addition of column, if DICTIONARY_INCLUDE and DICTIONARY_EXCLUDE are not specified, then the decision will be taken based on data type of the column. + +#### Examples: + +``` + ALTER TABLE carbon ADD COLUMNS (a1 INT, b1 STRING); +``` + +``` + ALTER TABLE carbon ADD COLUMNS (a1 INT, b1 STRING) TBLPROPERTIES('DICTIONARY_EXCLUDE'='b1'); +``` + +``` + ALTER TABLE carbon ADD COLUMNS (a1 INT, b1 STRING) TBLPROPERTIES('DICTIONARY_INCLUDE'='a1'); +``` + +``` + ALTER TABLE carbon ADD COLUMNS (a1 INT, b1 STRING) TBLPROPERTIES('DEFAULT.VALUE.a1'='10'); +``` + + +### **DROP COLUMNS** + +This command is used to delete a existing column or multiple columns in a table. + +``` + ALTER TABLE [db_name.]table_name DROP COLUMNS (col_name, ...); +``` + +#### Parameter Description +| Parameter | Description | +|------------|----------------------------------------------------------------------------------------------------------| +| db_Name | Name of the database. If this parameter is left unspecified, the current database is selected. | +| table_name | Name of the existing table. | +| col_name | Name of comma-separated column with data type. Column names contain letters, digits, and underscores (_) | + +#### Usage Guidelines + +- Deleting a column will also clear the dictionary files, provided the column is of type dictionary. + +- For delete column operation, there should be at least one key column that exists in the schema after deletion else error message will be displayed and the operation shall fail. + +#### Examples: + +If the table contains 4 columns namely a1, b1, c1, and d1. + +- **To delete a single column:** + +``` + ALTER TABLE carbon DROP COLUMNS (b1); +``` + +``` + ALTER TABLE test_db.carbon DROP COLUMNS (b1); +``` + + +- **To delete multiple columns:** + +``` + ALTER TABLE carbon DROP COLUMNS (b1,c1); +``` + +``` + ALTER TABLE carbon DROP COLUMNS (b1,c1); +``` + +### **CHANGE DATA TYPE** + +This command is used to change the data type from INT to BIGINT or decimal precision from lower to higher. + +``` + ALTER TABLE [db_name.]table_name CHANGE col_name col_name changed_column_type; +``` + +#### Parameter Description +| Parameter | Description | +|---------------------|-----------------------------------------------------------------------------------------------------------| +| db_Name | Name of the database. If this parameter is left unspecified, the current database is selected. | +| table_name | Name of the existing table. | +| col_name | Name of comma-separated column with data type. Column names contain letters, digits, and underscores (_). | +| changed_column_type | The change in the data type. | + +#### Usage Guidelines + +- Change of decimal data type from lower precision to higher precision will only be supported for cases where there is no data loss. + +#### Valid Scenarios +- Invalid scenario - Change of decimal precision from (10,2) to (10,5) is not valid as in this case only scale is increased but total number of digits remain the same. + +- Valid scenario - Change of decimal precision from (10,2) to (12,3) is valid as the total number of digits are increased by 2 but scale is increased only by 1 which will not lead to any data loss. + +- Note :The allowed range is 38,38 (precision, scale) and is a valid upper case scenario which is not resulting in data loss. + +#### Examples: +- **Changing data type of column a1 from INT to BIGINT** + +``` + ALTER TABLE test_db.carbon CHANGE a1 a1 BIGINT; +``` +- **Changing decimal precision of column a1 from 10 to 18.** + +``` + ALTER TABLE test_db.carbon CHANGE a1 a1 DECIMAL(18,2); +``` + ## DROP TABLE This command is used to delete an existing table. @@ -192,15 +363,15 @@ of columns is used. TBLPROPERTIES('BUCKETNUMBER'='noOfBuckets', 'BUCKETCOLUMNS'='columnname') ``` - -## Parameter Description + +### Parameter Description | Parameter | Description | Optional | |--------------- |------------------------------------------------------------------------------------------------------------------------------ |---------- | | BUCKETNUMBER | Specifies the number of Buckets to be created. | No | | BUCKETCOLUMNS | Specify the columns to be considered for Bucketing | No | -## Usage Guidelines +### Usage Guidelines - The feature is supported for Spark 1.6.2 onwards, but the performance optimization is evident from Spark 2.1 onwards. @@ -209,7 +380,7 @@ of columns is used. - Columns in the BUCKETCOLUMN parameter must be only dimension. The BUCKETCOLUMN parameter can not be a measure or a combination of measures and dimensions. -## Example : +### Example : ``` CREATE TABLE IF NOT EXISTS productSchema.productSalesTable ( @@ -228,33 +399,5 @@ of columns is used. 'NO_INVERTED_INDEX'='productBatch', 'BUCKETNUMBER'='4', 'BUCKETCOLUMNS'='productName') - ``` - -## TABLE RENAME - This command is used to rename the existing table. - -### Syntax -``` - ALTER TABLE [db_name.]table_name RENAME TO new_table_name; -``` - -### Parameter Description - -| Parameter | Description | -|---------------|-----------------------------------------------------------------------------------------------------------------------------------------------| -| db_name | Name of the database. If this parameter is left unspecified, the current database is selected. | -| table_name | Name of the existing table.| -|new_table_name | New table name for the existing table. | - -### Usage Guidelines -Following conditions must be met for successful rename operation: -* Queries running in parallel which requires the formation of path using the table name for reading carbon store files might fail during this operation. -* Secondary index table rename is not permitted. - -### Example: -``` - ALTER TABLE carbon RENAME TO carbondata; - - ALTER TABLE test_db.carbon RENAME TO test_db.carbondata; ```
