User Guide : Avalanche Data Loading Templates : Loading Google Cloud Storage Data to Avalanche on Google Cloud
 
Share this page                  
Loading Google Cloud Storage Data to Avalanche on Google Cloud
The Delimited_GCP_Storage_to_Avalanche_GCP template is used to quickly load large data files hosted on Google Cloud Storage into Avalanche database hosted on Google Cloud.
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 template to load files from Google Cloud Storage to Avalanche, you must perform the following:
• Confirm that the Avalanche data warehouse is running on GCP.
• Obtain credentials for connecting to the Avalanche database (user name and password)
• In case of Integration Manager On-premise, do one of 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 CR
For Linux, configure a driver entry called Ingres in the odbcinst.ini file (if it is not already present).
To load delimited data files from Google Cloud Storage to Avalanche data warehouse:
1. Select the Delimited_GCP_Storage_to_Avalanche_GCP configuration.
2. Set the values for the following required macros such as the connection string to your Avalanche data warehouse, credentials for the database and GCP bucket, and whether or not the first row of data contains a header for the column names. The connector used in the templates handles tab, comma, and pipe delimiters by default. If the source file has a different field separator, you can specify the delimiter using a macro.
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.
 
Macro Name
Description
$(AVALANCHE_CONNECT_STRING)
ODBC Connection string for connecting to Avalanche database. You can obtain this information from the Avalanche portal.
Note:  This is required only if you are running Integration Manager on DataCloud.
$(AVALANCHE_USERNAME)
$(AVALANCHE_PASSWORD)
Credentials used to connect to the database.
$(AVALANCHE_TABLE)
Name of the table in Avalanche where the data will be written to.
$(FILE_LIST)
Comma separated list of input files present in the GCP bucket. Files must have the same schema.
$(HEADER)
Specifies whether first data row contains column or field names. Used to detect field names for creating a new database table.
The following macros are used for temporarily staging the data on Google Cloud Platform storage:
 
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 data file with a typical record or field separators, 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
$(AVALANCHE_DSN)
Name of the ODBC data source to use 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.
$(FIELD_SEPARATOR)
Delimiter used in the source files to separate the fields.
$(RECORD_SEPARATOR)
Delimiter used in the source files to separate the data records.
$(QUOTE_CHARACTERS)
Character used to quote fields. For example, double quote ".
Use two characters if start and end quote characters are different. For example, [].
$(SAMPLE_SIZE)
Number of records that must be sampled for building the source schema.
$(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.
Default 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.
$(SOURCE_FETCH_SIZE)
Size of the data (in bytes) to fetch from the source file in the s3 bucket. Default is 15000000 (15MB).
$(AVALANCHE_DBADMIN_GROUP_ACCESS)
Grant table access to "dbadmingrp" group. Only applicable when new table is created. Default is True.
$(GCP_REGION)
Cloud storage location for specified bucket.
$(GCP_PROJECT_ID)
ID number for the project from GCP console.
$(TRUNCATION_HANDLING)
Specifies truncation handling for text data. The supported values are:
• ignore - Ignores the truncation and continues the execution. This is the default value.
• error - Logs an error message and aborts the execution.
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.

If you specify one of the first three macros (FIELD_SEPARATOR, RECORD SEPARATOR, or QUOTE CHARACTERS), then you must also specify the other two macros. Else, other delimiters are not auto-identified and use default values. For example, if you specify only QUOTE CHARACTER, then FIELD_SEPARATOR defaults to "," and RECORD_SEPARATOR defaults to "\r\n"(Windows) and "\n"(Linux).

Specify VWLOAD options as a macro in the VW_XXX format. XXX can be any of the properties listed in the COPY VWLOAD section in the Avalanche documentation. One macro can be added for each property. For properties such as STRICTNULLS that does not accept a value, the macro value must be the name of the property itself.

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