User Guide : Avalanche Data Loading Templates : Loading Actian Zen Data into Avalanche on Google Cloud
 
Share this page                  
Loading Actian Zen Data into Avalanche on Google Cloud
The Zen_to_Avalanche_Google template is used to load data from Actian Zen database tables into a table in Actian Avalanche data warehouse hosted on the Google Cloud Platform storage. This template requires an agent to run.
When you run a configuration with this template, it creates a new table if it does not exist or inserts data to an existing table of the same name if it exists.
Before using the Zen template to load data from a remote Actian Zen instance to Avalanche, you must perform the following:
• Download, install, and register the Agent. For instructions to install the agent, see <<link to Installing Agent section in IM doc>>. For instructions to register the agent, see <<link to register agent section in IM doc>>
– Agent enables the Zen template to connect to remote Zen installations.
– Agent must be installed on a system that can access the Zen instance you want to use.
– After registering the agent, make sure it is healthy.
• Install the Actian Zen client.
• Install and configure the Avalanche client on the same system where the Agent is installed.
• From the Warehouses list in Avalanche, click the warehouse that you will be writing to from the Zen template. In IP Allow List, add the IP address of the system where the Agent is running.
• Zen template provides two different protocols for connecting to Zen—JDBC and ODBC. If you want to use ODBC, install and configure the Zen ODBC client on same system the Agent is installed on. If you want to use JDBC, the JDBC client jar file is already packaged with the template and does not require any additional configuration.
• If you are using ODBC protocol, then do the following:
– Create ODBC DSN (as a System DSN) with the ODBC driver shipped with latest client runtime, which can be downloaded from https://esd.actian.com/ (select Product as Actian Avalanche, Release as Client Runtime, and Platform as Windows/Linux)
Setup ODBC DSN (as a System DSN) pointing to the Avalanche GCP instance
– In the connect string, replace the name of the driver based on the operating system:
For Windows, Driver=Actian
For Linux, configure a driver entry called Ingres in the odbcinst.ini file (if it is not already present). By default, the connect string is displayed as Ingres. Identifying the connection string to the specified warehouse
• Obtain the following:
– Credentials for connecting to the Avalanche database (user name and password)
– Actian Zen connection information
– Credentials for the database and GCP bucket
To load Actian Zen data into an Avalanche data warehouse:
1. Select the Zen_to_Avalanche_Google configuration.
2. Set the values for the following required macros.
Note:  You must provide values for these macros so that the Template has the necessary information to access your data files, Avalanche warehouse, and find or create your table.
You can encrypt sensitive macro values (such as passwords, secret keys, and tokens for token based authentication) from Integration Manager. For information about encrypting, see Enable Macro Encryption section in Integration Manager documentation available at docs.actian.com.
 
Macro Name
Description
$(AVALANCHE_CONNECT_STRING)
ODBC Connection string for connecting to Avalanche database. You can obtain this information from the Avalanche portal.
$(AVALANCHE_USERNAME), $(AVALANCHE_PASSWORD)
Credentials used to connect to the Avalanche database.
$(AVALANCHE_TABLE)
Name of the table in Avalanche, where the data will be written to.
$(ZEN_SERVER)
Hostname or IP address of the Actian Zen database server.
$(ZEN_DATABASE_NAME)
Name of the Zen database instance.
$(ZEN_USERNAME)/$(ZEN_PASSWORD)
Credentials to access Zen database.
$(ZEN_TABLE)
Name of the table in Zen database.
$(ZEN_QUERY)
SQL Statement for fetching Zen table data. Value can be query statement itself or a path to the query file. In case of path, prefix the value with file:///.
Note:  Specify either Zen_TABLE or Zen_QUERY macro.
The following macros are used for temporarily staging the data in a GCP bucket.
 
Macro Name
Description
$(GCP_BUCKET)
Name of the bucket in Google Cloud Platform storage to use for staging the data.
$(GCP_CLIENT_EMAIL)
Service account client email from GCP console.
$(GCP_PRIVATE_KEY)
Private key object for the service account in PKCS#8 format.
$(GCP_CLIENT_ID)
Service account client ID from GCP console.
$(GCP_PRIVATE_KEY_ID)
Private key identifier for the service account from GCP console.
3. If you want to override any of the default settings, you can add the following macros and set the values. Usually, these are not required unless you have a specific data format/encoding or want to increase the sample size, or want to specify the table operation. These macros are not displayed by default. You can manually add the name and set the value to override the default value.
 
Macro Name
Description
$(ZEN_TRNS_ISOLATION)
Transaction isolation setting for the ODBC connection. The supported values are:
• serializable
• repeatable read'
• read committed
• read uncommitted'
$(ZEN_WHERE_STMT)
Where statement to filter source records. Statement must exclude the 'WHERE' keyword and be syntactically correct as per Zen's SQL grammar.
Note:  Not applicable if query statement is used instead of table.
$(ZEN_JDBC_DRIVER_JAR_LOCATION)
If you want to use JDBC instead of ODBC for connecting to Zen, then you can specify Zen JDBC driver jar location using this macro. Value can be a path to the directory containing jar files or the jar file itself.
$(ZEN_CONNECTION_TYPE)
Specify the connector that must be used for connecting to Zen. Value are jdbc or odbc.
$(AVALANCHE_DSN)
Name of the ODBC data source for connecting to Avalanche database. Specify this macro if you want to use a pre-configured DSN on your system instead of the connect string.
$(AVALANCHE_CREATE_TABLE_QUERY)
Create table statement to use for creating the table. Make sure partitioning is specified. Table name in the query must match the AVALANCHE_TABLE macro value.
$(AVALANCHE_CREATE_TABLE_OPTIONS)
Use this option when you do not want to build the complete query but only want to specify options to pass to "with" clause of create table query.
Note:  Make sure partitioning is specified. Also, this macro is ignored if AVALANCHE_CREATE_TABLE_QUERY macro is defined.
$(OUTPUT_MODE)
Table operations that must be performed before inserting data. The available operations are:
• replace: Drops existing table and creates new table
• delete_append: Truncates table before inserting.
• append: Creates table only if it does not exist and inserts records.
The default value is append.
$(DEFAULT_TEXT_COL_SIZE)
Set the default size of the text columns in the table. Set it to a reasonable value based on your data to avoid truncations.
This property is also useful for inserting double-byte characters like Japanese or Chinese. Varchar is used for text data types that supports single byte characters. To support double-byte characters in varchar data type, the size of the column must be doubled using this macro.
$(UNICODE_CHARS)
Indicates whether the data contains Unicode characters. If set to True, nvarchar data type is used for text columns.
$(BATCH_SIZE)
Size of the chunk (in terms of number of records), the source is split into. Default is 50000.
$(COPYVW_PARALLEL_LOAD_SIZE)
Number of chunks to load in parallel using COPY VWLOAD. The default value is 20.
$(AVALANCHE_DBADMIN_GROUP_ACCESS)
Grant table access for "dbadmingrp" group. Only applicable when new table is created. Default is True.
$(TRUNCATION_HANDLING)
Specifies truncation handling for text data.
Supported values are:
• ignore - Ignores the truncation and continues the execution.
• warn - Logs a warning about the truncation and continues the execution.
• error - Logs an error message and aborts the execution.
Default value is ignore.
Note:  
You can specify individual column sizes by defining macros in the format COL_SIZE_XXX, where XXX is the source field name in uppercase. This is only applicable for fields of text type.

You can specify the VWLOAD options with macro names in the VW_XXX format, where XXX is the property name listed in the COPY VWLOAD section in the Avalanche documentation. One macro can be added for each property. For example:
VW_STRICTNULLS = STRICTNULLS

FDELIM, RDELIM, QUOTE, and GCP credentials must be specified using specific macros for these properties.
4. Set the Run Location in configuration details to User-Agent.
5. Run the configuration.
If there are any errors during job execution, see Troubleshooting Template Configuration Execution Errors for the troubleshooting information.