Contents x
    Template ETL
    • 17 Jul 2024
    • Print
    • PDF
    Contents

    Template ETL

    • Updated on 17 Jul 2024
    • Print
    • PDF
    Article summary

    summary

    This help page explains each item of the template ETL that can be set in STEP 2 Advanced Settings of the transfer settings.

    column definition

    Specify the columns to be transferred by narrowing or adding columns from the data retrieved from the transfer source set in STEP 1.
    The following changes can be made to the original column
    - column name
    - data type
    - default value
    - Date and time format (for date and time data)

    If the column from which you are transferring has changed, you can update it to the latest definition by clicking Reload Column Definitions.
    For more information, see Reloading Column Definitions.

    columndefinition.png

    Expand JSON columns

    If a field in the data retrieved from the transfer source contains a JSON type value, you can choose to expand the JSON column in the column definition.
    For data stored in JSON, it is expanded as a column by specifying the column name, JSON path, data type, date format, and time zone.
    For the expanded column name, enter a unique value that does not duplicate other column names.

    jsoncolumn.png

    For details on obtaining values when arrays are included in JSON, please refer to " How to Obtain Values by Specifying Elements when Arrays are Included in the Source Column's JSON ".

    Batch conversion of column names and data types

    The naming conventions and data types of selected columns can be converted in a batch.
    Select the checkbox to the left of each column and click Batch Conversion to display a dialog box for batch conversion.
    Note that data type conversion is supported only for conversion to string type.

    image.png

    Column Name Rule Conversion

    You can choose from the following four types

    Conversion FormatConversion Examplesummary
    capitalizationAccountId -> ACCOUNTID
    account_id -> ACCOUNT_ID    		Converts lowercase letters to uppercase.
    Symbolic characters such as hyphens ( - ) and underscores (_) are retained.
    Snake case (snake_case) conversionAccountId -> account_id
    account id -> account_id    		Convert CamelCase to SnakeCase.
    All uppercase letters are converted to lowercase.
    Note that single-byte spaces are also converted to underscores ( _).
    Upper Snake Case (UPPER_SNAKE_CASE) ConversionAccountId -> ACCOUNT_ID
    account id -> ACCOUNT_ID    		Convert CamelCase to SnakeCase.
    All lowercase letters are converted to uppercase.
    Note that single-byte spaces are also converted to underscores ( _).
    Sanitize (Google BigQuery column name compatible) conversionaccount.id-> account_id
    account id -> account_id    		Convert in a manner consistent with Google BigQuery column name conventions.
    Dots ( . ) and hyphens ( - ), and one-byte spaces are converted to underscores ( _).
    Multibyte characters such as Japanese are also converted to underscore ( _). Please note

    Filter settings

    Set AND or****OR conditions to narrow down rows of data.
    Only rows that match the criteria are forwarded.
    You can specify the target column name and filter value and select the following operators

    • >
    • >=
    • <
    • <=
    • ==
    • ! = !
    • start_with
    • end_with
    • include
    • IS NULL
    • IS NOT NULL
    • REGEXP

    filter.png

    Masking Settings

    Replaces the specified portion of the specified field with *.
    Rows of data that you do not want to transfer in their original form, such as personal information, can be transferred in a masked state.
    There are four types of masking, each as follows

    typeDescription.
    full character stringReplace all with *.
    Email AddressReplace up to the @ mark with *.
    (computer) regular expressionReplaces regular expression pattern matches with *.
    substringSpecify the start and end position and replace with *.

    masking.png

    Transfer Date Column Setting

    Set to add a column to record the start date and time of the transfer job execution.
    Please use this function when partitioning or sharding tables by the date and time the transfer job was executed.

    To add a transfer date column, check the Add transfer date column checkbox and specify the output format in the field below.

    image.png

    item nameDescription.
    Column name to be addedEnter the name of the column that outputs the transfer date and time.
    data typeSelect a data type from the following
  • timestamp
  • string
    • (computer) formatSpecify the format of the transfer date and time to be output.
    Can be specified only when string is selected as the data type.
    If timestamp is selected as the data type, it is fixed to %Y-%m-%d %H:%M:%S.%N %Z.

    The handling of time zone specifier ( %Z ) of timestamp type depends on the destination service.
    The format shown in the preview (e.g., 2023-12-01 15:30:45 UTC ) may differ from the format of the transferred timestamp.
    time zoneSelect the time zone for the output transfer date and time.
    Available only when string is selected for data type.
  • Etc/UTC
  • Asia/Tokyo

    • Example: If you select Asia/Tokyo
  • JST is deployed in format%Z
  • The format%z is expanded to +0900 (time difference from Etc/UTC )

    • If timestamp is selected as the data type, it is fixed to Etc/UTC.

    String Regular Expression Replacement

    Replaces the portion of a particular column that matches the specified regular expression with another string.
    This function can be used in data formatting, such as deleting spaces and symbols to correct notation distortion.
    For more information, see About String Regular Expression Replacement.

    column hashing

    Columns that you do not want to transfer as original data, such as personal information, are converted to hashed data using SHA-256 and transferred.
    Please note that this is not strictly encryption.

    character string conversion

    Converts a string of type String by specifying the target column name and conversion method.
    You can normalize full-width alphanumeric characters to half-width characters, half-width kana to full-width characters, etc.
    For more information, see String Conversion Function.

    UNIX time conversion

    Convert UNIX time and date/time formats by specifying the target column name and conversion method.

    item nameDescription.
    Target column nameEnter the name of the column to which the UNIX time conversion will be applied.
    Enter the column name set in the column definition.
    Conversion MethodSelect a conversion method from the following
    • UNIX time -> date/time (TIMESTAMP type)
    • UNIX time -> date/time (STRING type)
    • Date and time (TIMESTAMP type) -> UNIX time
    • Date and time (STRING type) -> UNIX time
    Subsequent setting items differ depending on the specified value.
    See the case where the conversion method is " UNIX time -> date/time" and the case where the conversion method is "date/time -> UNIX time," respectively.

    When the conversion method is "UNIX time -> date/time

    This is a setting item when "UNIX time -> date/time (TIMESTAMP type)" or "UNIX time -> date/time (STRING type)" is selected as the change method.

    item nameDescription.
    UNIX time units before conversionSelect the unit of UNIX time before conversion from the following
    • second (60th min)
    • millisecond
    • microsecond
    • nanosecond
    Converted formatSpecifies the date and time format after conversion.
    This can be specified only when "UNIX time -> Date/Time (STRING type)" is selected as the conversion method.
    If you select "UNIX time -> date/time (TIMESTAMP type)" as the conversion method, it will be fixed to %Y-%m-%d %H:%M:%S.%N %Z.

    The handling of the time zone specifier ( %Z ) of the TIMESTAMP type depends on the destination service.
    The format shown in the preview (e.g., 2023-12-01 15:30:45 UTC ) may differ from the format of the timestamp after transfer.
    Converted time zoneSelect the time zone for the converted date and time.
    This can be selected only when "UNIX Time -> Date/Time (STRING type)" is selected as the conversion method.
    If you select "UNIX time -> date/time (TIMESTAMP type)" as the conversion method, it will be fixed to Etc/UTC.

    When the conversion method is "Date/Time -> UNIX Time".

    This is the setting item when "Date/Time (TIMESTAMP type) -> UNIX time" or "Date/Time (STRING type) -> UNIX time" is selected as the change method.

    item nameDescription.
    Format before conversionSpecifies the date/time format before conversion.
    This can be specified only when "Date/Time (STRING type) -> UNIX time" is selected as the conversion method.
    If you select "UNIX time -> Date/Time (TIMESTAMP type)" for data type, the data type is fixed to %Y-%m-%d %H:%M:%S.%N %Z.
    Time zone before conversionSelect the time zone for the date and time before conversion.
    This can only be selected when "Date/Time (STRING type) -> UNIX time" is selected as the conversion method.
    If you select "UNIX time -> Date/Time (TIMESTAMP type)" for the data type, the data type is fixed to Etc/UTC.
    Converted UNIX time unitsSelect the unit of UNIX time after conversion from the following
    • second (60th min)
    • millisecond
    • microsecond
    • nanosecond
    Was this article helpful?
    What's Next