Defining a Schema Using the HCatalog Connector
After you set up the HCatalog Connector, you can use it to define a schema in your Vertica database to access the tables in a Hive database. You define the schema using the CREATE HCATALOG SCHEMA statement.
When creating the schema, you must supply the name of the schema to define in Vertica. Other parameters are optional. If you do not supply a value, Vertica uses default values. Vertica reads some default values from the HDFS configuration files.
After you define the schema, you can query the data in the Hive data warehouse in the same way you query a native Vertica table. The following example demonstrates creating an HCatalog schema and then querying several system tables to examine the contents of the new schema. See Viewing Hive Schema and Table Metadata for more information about these tables.
=> CREATE HCATALOG SCHEMA hcat WITH HOSTNAME='hcathost' HCATALOG_SCHEMA='default' HIVESERVER2_HOSTNAME='hs.example.com' HCATALOG_USER='admin'; WARNING 0: HOSTNAME will be ignored. hive-site.xml must be contain property [hive.metastore.uris], or both HOSTNAME and PORT must be specified CREATE SCHEMA => \x Expanded display is on. => SELECT * FROM v_catalog.hcatalog_schemata; -[ RECORD 1 ]----------------+------------------------------------------- schema_id | 45035996273748224 schema_name | hcat schema_owner_id | 45035996273704962 schema_owner | admin create_time | 2017-05-22 12:04:59.692155-04 hostname | hcathost port | -1 hiveserver2_hostname | hs.example.com webservice_hostname | webservice_port | 50111 webhdfs_address | hs.example.com:50070 hcatalog_schema_name | default hcatalog_user_name | admin hcatalog_connection_timeout | -1 hcatalog_slow_transfer_limit | -1 hcatalog_slow_transfer_time | -1 => SELECT * FROM v_catalog.hcatalog_table_list; -[ RECORD 1 ]------+------------------ table_schema_id | 45035996273748224 table_schema | hcat hcatalog_schema | default table_name | nation hcatalog_user_name | admin -[ RECORD 2 ]------+------------------ table_schema_id | 45035996273748224 table_schema | hcat hcatalog_schema | default table_name | raw hcatalog_user_name | admin -[ RECORD 3 ]------+------------------ table_schema_id | 45035996273748224 table_schema | hcat hcatalog_schema | default table_name | raw_rcfile hcatalog_user_name | admin -[ RECORD 4 ]------+------------------ table_schema_id | 45035996273748224 table_schema | hcat hcatalog_schema | default table_name | raw_sequence hcatalog_user_name | admin