Skip to main content

BigQuery Table Settings

When publishing to BigQuery, please complete the following steps to configure the table and settings to apply to the publish action.

Steps:

  1. Select location: Navigate the BigQuery browser to select the database and table to which to publish.

    1. To create a new table, click Create a new table.

  2. Select table options:

    1. Table name:

      Note

      BigQuery does not support destinations with a dot (.) in the name. For more information, see https://cloud.google.com/bigquery/docs/tables#table_naming.

      1. New table: Enter a name for it. You may use a pre-existing table name, and schema checks are performed against it.

      2. Existing table: You cannot modify the name.

    2. Allow strict rules for type mismatch:

      Note

      This feature may not be available in all product editions. For more information on available features, see Compare Editions.

      1. When enabled, the data types and their formatting set in the Trifacta Application must match the types and formatting supported in BigQuery.

        Note

        By default, data is published to BigQuery using strict data type matching rules. When there are mismatches between Alteryx data types and BigQuery data types, the publication fails.

      2. When disabled, Alteryx data types are written to the closest approximation of time and formatting in BigQuery.

      3. For more information, see BigQuery Data Type Conversions.

    3. Output database: To change the database to which you are publishing, click the BigQuery icon in the sidebar. Select a different database.

    4. Publish actions: Select one of the following.

      1. Create new table every run: Each run generates a new table with a timestamp appended to the name.

      2. Append to this table every run: Each run adds any new results to the end of the table.

      3. Truncate the table every run: With each run, all data in the table is truncated and replaced with any new results.

      4. Drop the table every run: With each run, the table is dropped (deleted), and all data is deleted. A new table with the same name is created, and any new results are added to it.

      5. Merge the table every run: This publishing option merges the rows in your results with any existing rows in the target BigQuery table. For more information, see Merge Table Operations below.

      6. More options: With each run, you can allow the following rules for strict type matching:

        • Datetime: Select to publish datetime to BigQuery as one of several date types.

        • Array: Select to publish Array types are published as BigQuery arrays.

          Note

          You can publish to BigQuery arrays if the data is homogeneous.

          Note

          BigQuery supports nested data up to 15 levels deep. Data that is nested deeper than 15 levels results in an error on publication.

        • Object: Select to publish Objects and Array of Objects as BigQuery structs.

          Note

          BigQuery supports nested data up to 15 levels deep. Data that is nested deeper than 15 levels results in an error on publication.

        • For more information, see BigQuery Data Type Conversions.

  3. To save the publishing action, click Add or Update.

Merge Table Operations

Note

When a merge operation is performed on a BigQuery table, profiling information displayed in the Job Details page is incorrectly based on source data. Downloaded profile information is accurate. This is a known issue.

The publishing option to merge table with every run allows you to update existing rows of data in the target table with corresponding values from your results (merge) and optionally to insert or delete matching rows from your results into the table.

Steps:

  1. In the Table Settings panel, select Merge the table every run.

  2. Primary keys for matching: Select one or more columns whose values determine if a row in your source results matches a row in the target. When these key values match, the following columns are updated.

    Note

    Columns of Array or Object data type cannot be used as key columns for merge operations.

    Note

    Null values in a key column in the source table are not considered matches with null values in the key columns of the target table. Instead, where appropriate, a new row is inserted in the target column containing only the values from the source table.

    1. If the matching columns have duplicate rows in the target table, all rows in the target are updated.

    2. If the matching columns have duplicate rows in the source, the job fails.

  3. Action on target table for matched rows: Select the action to apply to the target record when a match is found between the key columns:

    1. Update: The values from your results are updated into the columns specified below.

    2. Delete: The row in the target table is deleted.

  4. Keys to be updated: Select one or more columns whose values are updated from your source results when values from the previous set of columns match. These are the columns that are merged into the table.

    Tip

    If All Columns is selected, all columns other than the matching columns are updated on a match. All columns continue to be updated even if the schema changes, and the matching columns remain in the schema.

  5. Insert source rows if no match in target:

    1. When selected, rows in your source that do not have a matching set of values in key columns are inserted into the table as new rows.

    2. When deselected, these unmatched rows are not written to the target table.

  6. Delete target rows if no match in source:

    1. When selected, all rows in the target that do not have a matching set of key fields your source results are deleted.

    2. When deselected, unmatched rows in the target are not deleted.

In this section: