Create External Table BQ
    • Dark
      Light

    Create External Table BQ

    • Dark
      Light

    Article Summary

    This article is specific to the following platforms - BigQuery.

    Create External Table

    Create a table that references data stored in an external storage system, such as Google Cloud Storage.

    For full information on working with tables on Google Cloud Platform, see the official documentation here.


    BigQuery Properties

    PropertySettingDescription
    NameTextA human-readable name for the component.

    This is automatically determined from the table name when the Table Name property is first set.
    ProjectTextEnter the name of the Google Cloud Platform Project that the table belongs to.
    DatasetTextEnter the name of the Google Cloud Platform Dataset that the table belongs to.
    New Table NameTextSelect an existing table to load data into.
    Table MetadataColumn NameThe name of the new column
    Data TypeFor more information on available BigQuery data types please refer to the GCP documentation.
    String: this type can hold any kind of data, subject to a maximum size.
    Integer: this type is suitable for whole-number types (no decimals).
    Float: this type is suitable for numeric types, with or without decimals.
    Numeric: this data type is suitable for data of an exact numeric value, allowing 38 digits of precision and 9 decimal digits of scale.
    Boolean: this type is suitable for data that is either 'true' or 'false'.
    Date: a formatted date object without time. See the GCP documentation.
    Time: a formatted time object without date. See the GCP documentation.
    DateTime: a formatted timestamp containing both date and time that is easily readable by the user. See the GCP documentation.
    Timestamp: this type is a timestamp left unformatted (exists as Unix/Epoch Time).
    ModeThe field mode. Default is 'NULLABLE'.
    NULLABLE: Field allows null values
    REQUIRED: Field does not accept null values
    REPEATED: Field can accept multiple values
    Table Metadata (Nested)When the Define Nested Metadata checkbox is ticked inside the Table Metadata property, a tree-structure can be defined for metadata. See Example 2 at the bottom of the full documentation for details.
    Create/ReplaceSelectCreate: The default option, creates a new table. This will generate an error if a table with the same name already exists, but will never destroy existing data.
    Create if not exists: This will only create a new table if a table of the same name does not already exist. It will not destroy existing data. If the schema of the existing table does not match the schema defined in this component, no attempt is made to fix or correct it, which could lead to errors later in the job if you did not expect an existing table to exist, or to have a different schema to the one defined in this component.
    Replace This drops any existing table of the same name, and then creates a new table. This guarantees that after the component succeeds the table matches the schema defined in this component, however any existing data in an existing table will be lost.Note: Since other database objects depend upon this table,
    drop ... cascade
    is used which may actually remove many other database objects.
    Google Storage URL LocationSelectThe URL of the Google Storage bucket to get the files from. This follows the format gs://bucket-name/location, where location is optional.
    CompressionSelectWhether the input file is compressed in gzip format or not compressed at all.
    File FormatSelectCloud Datastore Backup
    CSV
    JSON (New line delimited): this requires an additional "JSON Format".
    Number of Errors AllowedTextThe maximum number of individual parsing errors that cause the whole load to fail. Values up to this will be substituted as null values. This value defaults to 0.
    Ignore Unknown ValuesSelectYes: Accept rows that contain values that do not match the schema. Unknown values are ignored. Will ignore extra values at the end of a line for CSV files.
    No: Omit any rows with invalid values.
    DelimiterSelectThe delimiter that separates columns. The default is a Comma. A [TAB] character can be specified as "\ ".
    CSV QuoterTextSpecifies the character to be used as the quote character when using the CSV option.
    EncodingSelectThe encoding the data is in. This defaults to UTF-8.
    Header Rows To SkipTextThe number of rows at the top of the file to ignore - defaults to 0.
    Allow quoted newlinesSelectYes: Allow a CSV value to contain a newline character when the value is encased in quotation marks.
    No: A new line character, regardless of quotations, is always considered a new row.
    Allow Jagged RowsSelectYes: Missing values are treated as 'null' but accepted.
    No: Rows with missing data are treated as bad records. Note: A bad record will count toward the 'Maximum Errors' count.