SYNC_WITH_HCATALOG_SCHEMA_TABLE
Copies the structure of a single table in a Hive database schema available through the HCatalog Connector to a Vertica table.
Syntax
SYNC_WITH_HCATALOG_SCHEMA_TABLE( local_schema, hcatalog_schema, table_name )
Parameters
local_schema |
The existing Vertica schema to store the copied HCatalog schema's metadata. This can be the same schema as hcatalog_schema, or it can be a separate one created using CREATE SCHEMA. |
hcatalog_schema |
The HCatalog schema to copy. This is the schema created using CREATE HCATALOG SCHEMA. |
table_name | The table in hcatalog_schema to copy. |
Notes
If table_name does not exist in hcatalog_schema, this function returns an error.
If table_name already exists in local_schema, the SYNC_WITH_HCATALOG_SCHEMA_TABLE function overwrites it.
Hive STRING and BINARY data types are matched, in Vertica, to VARCHAR(65000) and VARBINARY(65000) types. You can use ALTER TABLE to adjust these after creating the schema. The maximum size of a VARCHAR or VARBINARY in Vertica is 65000, but you can use LONG VARCHAR and LONG VARBINARY to specify larger values.
Hive and Vertica define string length in different ways. In Hive the length is the number of characters; in Vertica it is the number of bytes. Thus, a character encoding that uses more than one byte, such as Unicode, can result in mismatches between the two. Set values in Vertica based on bytes, not characters, to avoid data truncation.
If the data size exceeds the declared size for the column, Vertica logs an event at read time in the QUERY_EVENTS system table.
This function can synchronize the HCatalog schema directly, in which case you call it with the same schema name for both of the local_schema and hcatalog_schema parameters. It can also synchronize a different schema to the HCatalog schema.
Privileges
The user must have CREATE privileges on local_schema and USAGE permissions on hcatalog_schema.
Examples
The following example shows using SYNC_WITH_HCATALOG_SCHEMA_TABLE to synchronize the "nation" table:
=> CREATE HCATALOG SCHEMA hcat WITH hostname='hcathost' HCATALOG_SCHEMA='hcat_truth' -> HCATALOG_USER='hcatuser'; CREATE SCHEMA => SELECT sync_with_hcatalog_schema_table('hcat', 'hcat_truth', 'nation'); sync_with_hcatalog_schema_table ----------------------------------------------------------------------------- Schema hcat synchronized with hcat_truth for table nation table nation is created in schema hcat (1 row)
The following example shows the behavior if the "nation" table already exists in the local schema:
=> SELECT sync_with_hcatalog_schema_table('hcat','hcat_truth','nation'); sync_with_hcatalog_schema_table ----------------------------------------------------------------------------- Schema hcat synchronized with hcat_truth for table nation table nation is altered in schema hcat (1 row)