Contents x
    Template ETL
      • Print
      • PDF
      Contents

      Template ETL

        • Print
        • PDF
        Article summary

        summary

        This help page explains each Template ETL item that can be set in STEP 2 Advanced Configuration of ETL Configuration.

        Column Setting

        From the Data Source retrieved from the ETL Configuration in STEP 1, filter or add columns and specify the columns to be forwarded.
        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 Data Source column has changed, it can be updated to the latest definition by clicking Reload Column Setting.
        For more information, see Reloading Column Setting.

        template-etl-2024-08-29-22-47-0

        Expand JSON columns

        If a field in the data retrieved from Data Source contains a value of type JSON, you can choose to expand the JSON column in the Column Setting.
        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.

        template-etl-2024-08-29-22-47-1

        For details on retrieving values when JSON contains ar rays, please refer to " How to retrieve values by specifying elements when the Data Source column's JSON contains arrays.

        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 the dialog 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 it in a way that is consistent with Google BigQuery's column name convention.
        Dots ( . ) and hyphens ( - ) and one-byte spaces are converted to underscores ( _).
        Multibyte characters such as Japanese are also converted to underscore ( _). Please note

        Filter Setting

        Data Setting is used to narrow down rows of data using either AND or****OR conditions.
        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

        template-etl-2024-08-29-22-47-3

        Masking Setting

        Replaces the specified portion of the specified field with *.
        Columns of data that you do not want to be transferred as Data Source, 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 *.

        template-etl-2024-08-29-22-47-4

        Transfer Date Column Setting

        Transfer Date Column Setting if you want to add a column that records the start date and time of ETL Job execution.
        Please use this option when partitioning or sharding tables by the date and time the ETL 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 asthe data type.
        If timestamp is selected asthe data type, it is fixed to %Y-%m-%d %H:%M:%S.%N %Z.

        The way the time zone specifier ( %Z ) of the timestamp type is handled depends on the Data 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 fordata 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 asthe data type, it is fixed to Etc/UTC.

        Regular Expression Replacement

        Replace 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 Regular Expression Replacement.

        Column Hashing

        The columns that you do not want to transmit as Data Source, such as personal information, are converted to a hashed version using SHA-256 and then transmitted.
        Please note that this is not strictly encryption.

        String Conversion

        Converts a String Conversion 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

        Specify the target column name and conversion method to perform UNIX Time conversion and date/time format conversion.

        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 Column Setting.
        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 thecase where the conversion method is "Date/Time -> UNIX Time", respectively.

        When the conversion method is "UNIX Time conversion -> 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 conversion units before conversionSelect the unit of UNIX Time 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 conversion -> Date/Time (STRING type)" is selected as the conversion method.
        If you select "UNIX Time conversion -> Date/Time (TIMESTAMP type)" as the conversion method, the conversion will be fixed to %Y-%m-%d %H:%M:%S.%N %Z.

        The way the time zone specifier ( %Z) of the TIMESTAMP type is handled depends on the Data 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.
        Available only when "UNIX Time conversion -> Date/Time (STRING type)" is selected as the conversion method.
        If you select "UNIX Time conversion -> Date/Time (TIMESTAMP type)" as the conversion method, the conversion is fixed to Etc/UTC.

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

        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 conversion " is selected as the conversion method.
        If you select "UNIX time -> Date/Time (TIMESTAMP type)" for data type, the datatype 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.
        Available only when "Date/Time (STRING type) -> UNIX Time conversion " is selected as the conversion method.
        If you select "UNIX time -> Date/Time (TIMESTAMP type)" for the data type, the datatype is fixed to Etc/UTC.
        UNIX Time conversion unitsSelect the unit of UNIX Time conversion from the following
        • second (60th min)
        • millisecond
        • microsecond
        • nanosecond
        Was this article helpful?
        What's Next