HCATALOG_COLUMNS
Describes the columns of all tables available through the HCatalog Connector. Each row in this table corresponds to to a column in a table accessible through the HCatalog Connector. See Using the HCatalog Connector in Integrating with Apache Hadoop for more information.
| Column Name | Data Type | Description |
|---|---|---|
TABLE_SCHEMA
|
VARCHAR(128) |
The name of the Vertica Analytic Database schema that contains the table containing this column |
HCATALOG_SCHEMA
|
VARCHAR(128) |
The name of the Hive schema or database that contains the table containing this column |
TABLE_NAME
|
VARCHAR(128) | The name of the table that contains the column |
IS_PARTITION_COLUMN
|
BOOLEAN | Whether the table is partitioned on this column |
COLUMN_NAME
|
VARCHAR(128) | The name of the column |
HCATALOG_DATA_TYPE
|
VARCHAR(128) | The Hive data type of this column |
DATA_TYPE
|
VARCHAR(128) | The Vertica Analytic Database data type of this column |
DATA_TYPE_ID
|
INTEGER | Numeric ID of the column's Vertica Analytic Database data type |
DATA_TYPE_LENGTH
|
INTEGER | The number of bytes used to store this data type |
CHARACTER_MAXIMUM_LENGTH
|
INTEGER | For string data types, the maximum number of characters it can hold |
NUMERIC_PRECISION
|
INTEGER | For numeric types, the precision of the values in the column |
NUMERIC_SCALE
|
INTEGER | For numeric data types, the scale of the values in the column |
DATETIME_PRECISION
|
INTEGER | For datetime data types, the precision of the values in the column |
INTERVAL_PRECISION
|
INTEGER | For interval data types, the precision of the values in the column |
ORDINAL_POSITION
|
INTEGER | The position of the column within the table |
Privileges
No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.
Notes
If you are using WebHCat instead of HiveServer2, querying this table results in one web service call to the WebHCat server for each table in each HCatalog schema. If you need to perform multiple queries on this table in a short period of time, consider creating a copy of the table using a CREATE TABLE AS statement to improve performance. The copy does not reflect any changes made to the schema of the Hive tables after it was created, but it is much faster to query.
Example
The following example demonstrates finding the column information for a specific table:
=> SELECT * FROM HCATALOG_COLUMNS WHERE table_name = 'hcatalogtypes' -> ORDER BY ordinal_position; -[ RECORD 1 ]------------+----------------- table_schema | hcat hcatalog_schema | default table_name | hcatalogtypes is_partition_column | f column_name | intcol hcatalog_data_type | int data_type | int data_type_id | 6 data_type_length | 8 character_maximum_length | numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | 1 -[ RECORD 2 ]------------+----------------- table_schema | hcat hcatalog_schema | default table_name | hcatalogtypes is_partition_column | f column_name | floatcol hcatalog_data_type | float data_type | float data_type_id | 7 data_type_length | 8 character_maximum_length | numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | 2 -[ RECORD 3 ]------------+----------------- table_schema | hcat hcatalog_schema | default table_name | hcatalogtypes is_partition_column | f column_name | doublecol hcatalog_data_type | double data_type | float data_type_id | 7 data_type_length | 8 character_maximum_length | numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | 3 -[ RECORD 4 ]------------+----------------- table_schema | hcat hcatalog_schema | default table_name | hcatalogtypes is_partition_column | f column_name | charcol hcatalog_data_type | string data_type | varchar(65000) data_type_id | 9 data_type_length | 65000 character_maximum_length | 65000 numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | 4 -[ RECORD 5 ]------------+----------------- table_schema | hcat hcatalog_schema | default table_name | hcatalogtypes is_partition_column | f column_name | varcharcol hcatalog_data_type | string data_type | varchar(65000) data_type_id | 9 data_type_length | 65000 character_maximum_length | 65000 numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | 5 -[ RECORD 6 ]------------+----------------- table_schema | hcat hcatalog_schema | default table_name | hcatalogtypes is_partition_column | f column_name | boolcol hcatalog_data_type | boolean data_type | boolean data_type_id | 5 data_type_length | 1 character_maximum_length | numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | 6 -[ RECORD 7 ]------------+----------------- table_schema | hcat hcatalog_schema | default table_name | hcatalogtypes is_partition_column | f column_name | timestampcol hcatalog_data_type | string data_type | varchar(65000) data_type_id | 9 data_type_length | 65000 character_maximum_length | 65000 numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | 7 -[ RECORD 8 ]------------+----------------- table_schema | hcat hcatalog_schema | default table_name | hcatalogtypes is_partition_column | f column_name | varbincol hcatalog_data_type | binary data_type | varbinary(65000) data_type_id | 17 data_type_length | 65000 character_maximum_length | 65000 numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | 8 -[ RECORD 9 ]------------+----------------- table_schema | hcat hcatalog_schema | default table_name | hcatalogtypes is_partition_column | f column_name | bincol hcatalog_data_type | binary data_type | varbinary(65000) data_type_id | 17 data_type_length | 65000 character_maximum_length | 65000 numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | 9