...

Expand

title	see example in JSON

Here is the example for the first case above (make sure to format it in JSONL before loading to BigQuery: Newline delimited JSON : https://en.wikipedia.org/wiki/JSON_streaming).

Code Block

language	json

tbd

Properties

...

Field name

...

Type

...

Mode

...

Description

...

*

...

CONTACT

...

REQUIRED

...

All the properties of the CONTACT defined type are also main parameters of the doc_user type

...

contacts

...

CONTACT

...

REPEATED

...

default contacts like for billing and shipping

...

creation_tm

...

DATETIME

...

REQUIRED

...

technical field

...

client_id

...

INTEGER

...

REQUIRED

...

technical field

...

src_sys_id

...

INTEGER

...

REQUIRED

...

technical field

Table structure

Expand

title	BigQuery JSON Schema

Code Block

language	json

[
      {
        "description": "the persona_id as referenced in other tables",
        "type": "STRING",
        "name": "persona_id",
        "mode": "REQUIRED"
      },
      {
        "type": "STRING",
        "name": "persona_type",
        "description": "the type of persona (user, guest, ...)"
      },
      {
        "type": "STRING",
        "name": "internal_id",
        "description": "an additional internal id (optional)"
      },
      {
        "type": "STRING",
        "name": "external_id",
        "description": "the external identifier of the user (can be the same as the internal identifier)"
      },
      {
        "type": "STRING",
        "name": "title"
      },
      {
        "type": "STRING",
        "name": "prefix"
      },
      {
        "type": "STRING",
        "name": "firstname"
      },
      {
        "type": "STRING",
        "name": "middlename"
      },
      {
        "type": "STRING",
        "name": "lastname"
      },
      {
        "type": "STRING",
        "name": "suffix"
      },
      {
        "type": "STRING",
        "name": "gender"
      },
      {
        "type": "DATETIME",
        "name": "date_of_birth"
      },
      {
        "type": "DATETIME",
        "name": "account_creation"
      },
      {
        "type": "STRING",
        "name": "creation_label"
      },
      {
        "type": "STRING",
        "name": "auto_group"
      },
      {
        "type": "STRING",
        "name": "invoice_status"
      },
      {
        "type": "STRING",
        "name": "status"
      },
      {
        "type": "STRING",
        "name": "spouse_id"
      },
      {
        "type": "STRING",
        "name": "children_ids",
        "mode": "REPEATED"
      },
      {
        "type": "STRING",
        "name": "customer_groups",
        "mode": "REPEATED"
      },
      {
        "description": "the stores",
        "type": "STRING",
        "name": "stores",
        "mode": "REPEATED"
      },
      {
        "description": "the stores",
        "type": "STRING",
        "name": "websites",
        "mode": "REPEATED"
      },
      {
        "type": "STRING",
        "name": "company"
      },
      {
        "type": "STRING",
        "name": "vat"
      },
      {
        "type": "BOOLEAN",
        "name": "vat_is_valid"
      },
      {
        "type": "STRING",
        "name": "vat_request_id"
      },
      {
        "type": "DATETIME",
        "name": "vat_request_date"
      },
      {
        "type": "BOOLEAN",
        "name": "vat_request_success"
      },
      {
        "type": "STRING",
        "name": "street"
      },
      {
        "type": "STRING",
        "name": "additional_address_line"
      },
      {
        "type": "STRING",
        "name": "city"
      },
      {
        "type": "STRING",
        "name": "zipcode"
      },
      {
        "type": "STRING",
        "name": "stateID"
      },
      {
        "type": "STRING",
        "name": "department"
      },
      {
        "type": "STRING",
        "name": "statename"
      },
      {
        "type": "STRING",
        "name": "countryiso"
      },
      {
        "type": "STRING",
        "name": "countryID"
      },
      {
        "type": "STRING",
        "name": "phone"
      },
      {
        "type": "STRING",
        "name": "email"
      },
      {
        "type": "STRING",
        "name": "mobile_phone"
      },
      {
        "type": "STRING",
        "name": "fax"
      },
      {
        "type": "STRING",
        "name": "giftregistry_item_id"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional string (not localized) attributes of the voucher",
        "type": "RECORD",
        "name": "string_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "STRING",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional localized string attributes of the voucher",
        "type": "RECORD",
        "name": "localized_string_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "type": "NUMERIC",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional numeric (not localized) attributes of the voucher",
        "type": "RECORD",
        "name": "numeric_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "NUMERIC",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional localized numeric attributes of the voucher",
        "type": "RECORD",
        "name": "localized_numeric_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "type": "DATETIME",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional datetime (not localized) attributes of the voucher",
        "type": "RECORD",
        "name": "datetime_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "language",
                "mode": "REQUIRED"
              },
              {
                "type": "DATETIME",
                "name": "value",
                "mode": "REQUIRED"
              }
            ],
            "type": "RECORD",
            "name": "values",
            "mode": "REPEATED"
          }
        ],
        "description": "additional localized datetime attributes of the voucher",
        "type": "RECORD",
        "name": "localized_datetime_attributes",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "type": "INTEGER",
            "name": "status",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "fields": [
                  {
                    "type": "STRING",
                    "name": "language",
                    "mode": "REQUIRED"
                  },
                  {
                    "type": "DATETIME",
                    "name": "value",
                    "mode": "REQUIRED"
                  }
                ],
                "type": "RECORD",
                "name": "start_datetime",
                "mode": "REPEATED"
              },
              {
                "fields": [
                  {
                    "type": "STRING",
                    "name": "language",
                    "mode": "REQUIRED"
                  },
                  {
                    "type": "DATETIME",
                    "name": "value",
                    "mode": "REQUIRED"
                  }
                ],
                "type": "RECORD",
                "name": "end_datetime",
                "mode": "REPEATED"
              }
            ],
            "type": "RECORD",
            "name": "periods",
            "mode": "REPEATED"
          }
        ],
        "description": "the state of the subscriptions (e-mails and others)",
        "type": "RECORD",
        "name": "subscriptions",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "type": "INTEGER",
            "name": "status",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "fields": [
                  {
                    "type": "STRING",
                    "name": "language",
                    "mode": "REQUIRED"
                  },
                  {
                    "type": "DATETIME",
                    "name": "value",
                    "mode": "REQUIRED"
                  }
                ],
                "type": "RECORD",
                "name": "start_datetime",
                "mode": "REPEATED"
              },
              {
                "fields": [
                  {
                    "type": "STRING",
                    "name": "language",
                    "mode": "REQUIRED"
                  },
                  {
                    "type": "DATETIME",
                    "name": "value",
                    "mode": "REQUIRED"
                  }
                ],
                "type": "RECORD",
                "name": "end_datetime",
                "mode": "REPEATED"
              }
            ],
            "type": "RECORD",
            "name": "periods",
            "mode": "REPEATED"
          }
        ],
        "description": "the state of the notofications (e-mails and others)",
        "type": "RECORD",
        "name": "notifications",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "type": "STRING",
            "name": "type"
          },
          {
            "type": "STRING",
            "name": "name",
            "mode": "REQUIRED"
          },
          {
            "type": "INTEGER",
            "name": "status",
            "mode": "REQUIRED"
          },
          {
            "fields": [
              {
                "fields": [
                  {
                    "type": "STRING",
                    "name": "language",
                    "mode": "REQUIRED"
                  },
                  {
                    "type": "DATETIME",
                    "name": "value",
                    "mode": "REQUIRED"
                  }
                ],
                "type": "RECORD",
                "name": "start_datetime",
                "mode": "REPEATED"
              },
              {
                "fields": [
                  {
                    "type": "STRING",
                    "name": "language",
                    "mode": "REQUIRED"
                  },
                  {
                    "type": "DATETIME",
                    "name": "value",
                    "mode": "REQUIRED"
                  }
                ],
                "type": "RECORD",
                "name": "end_datetime",
                "mode": "REPEATED"
              }
            ],
            "type": "RECORD",
            "name": "periods",
            "mode": "REPEATED"
          }
        ],
        "description": "the state of the vouchers (used and others)",
        "type": "RECORD",
        "name": "voucher_states",
        "mode": "REPEATED"
      },
      {
        "fields": [
          {
            "description": "e.g.: billing, shipping",
            "type": "STRING",
            "name": "type",
            "mode": "REQUIRED"
          },
          {
            "type": "STRING",
            "name": "persona_id",
            "description": "the persona_id as referenced in other tables"
          },
          {
            "type": "STRING",
            "name": "persona_type",
            "description": "the type of persona (user, guest, ...)"
          },
          {
            "type": "STRING",
            "name": "internal_id",
            "description": "an additional internal id (optional)"
          },
          {
            "type": "STRING",
            "name": "external_id",
            "description": "the external identifier of the user (can be the same as the internal identifier)"
          },
          {
            "type": "STRING",
            "name": "title"
          },
          {
            "type": "STRING",
            "name": "prefix"
          },
          {
            "type": "STRING",
            "name": "firstname"
          },
          {
            "type": "STRING",
            "name": "middlename"
          },
          {
            "type": "STRING",
            "name": "lastname"
          },
          {
            "type": "STRING",
            "name": "suffix"
          },
          {
            "type": "STRING",
            "name": "gender"
          },
          {
            "type": "DATETIME",
            "name": "date_of_birth"
          },
          {
            "type": "DATETIME",
            "name": "account_creation"
          },
          {
            "type": "STRING",
            "name": "creation_label"
          },
          {
            "type": "STRING",
            "name": "auto_group"
          },
          {
            "type": "STRING",
            "name": "invoice_status"
          },
          {
            "type": "STRING",
            "name": "status"
          },
          {
            "type": "STRING",
            "name": "spouse_id"
          },
          {
            "type": "STRING",
            "name": "children_ids",
            "mode": "REPEATED"
          },
          {
            "type": "STRING",
            "name": "customer_groups",
            "mode": "REPEATED"
          },
          {
            "description": "the stores",
            "type": "STRING",
            "name": "stores",
            "mode": "REPEATED"
          },
          {
            "description": "the stores",
            "type": "STRING",
            "name": "websites",
            "mode": "REPEATED"
          },
          {
            "type": "STRING",
            "name": "company"
          },
          {
            "type": "STRING",
            "name": "vat"
          },
          {
            "type": "BOOLEAN",
            "name": "vat_is_valid"
          },
          {
            "type": "STRING",
            "name": "vat_request_id"
          },
          {
            "type": "DATETIME",
            "name": "vat_request_date"
          },
          {
            "type": "BOOLEAN",
            "name": "vat_request_success"
          },
          {
            "type": "STRING",
            "name": "street"
          },
          {
            "type": "STRING",
            "name": "additional_address_line"
          },
          {
            "type": "STRING",
            "name": "city"
          },
          {
            "type": "STRING",
            "name": "zipcode"
          },
          {
            "type": "STRING",
            "name": "stateID"
          },
          {
            "type": "STRING",
            "name": "department"
          },
          {
            "type": "STRING",
            "name": "statename"
          },
          {
            "type": "STRING",
            "name": "countryiso"
          },
          {
            "type": "STRING",
            "name": "countryID"
          },
          {
            "type": "STRING",
            "name": "phone"
          },
          {
            "type": "STRING",
            "name": "email"
          },
          {
            "type": "STRING",
            "name": "mobile_phone"
          },
          {
            "type": "STRING",
            "name": "fax"
          },
          {
            "type": "STRING",
            "name": "giftregistry_item_id"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "type"
              },
              {
                "type": "STRING",
                "name": "name",
                "mode": "REQUIRED"
              },
              {
                "type": "STRING",
                "name": "values",
                "mode": "REPEATED"
              }
            ],
            "description": "additional string (not localized) attributes of the voucher",
            "type": "RECORD",
            "name": "string_attributes",
            "mode": "REPEATED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "type"
              },
              {
                "type": "STRING",
                "name": "name",
                "mode": "REQUIRED"
              },
              {
                "fields": [
                  {
                    "type": "STRING",
                    "name": "language",
                    "mode": "REQUIRED"
                  },
                  {
                    "type": "STRING",
                    "name": "value",
                    "mode": "REQUIRED"
                  }
                ],
                "type": "RECORD",
                "name": "values",
                "mode": "REPEATED"
              }
            ],
            "description": "additional localized string attributes of the voucher",
            "type": "RECORD",
            "name": "localized_string_attributes",
            "mode": "REPEATED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "type"
              },
              {
                "type": "STRING",
                "name": "name",
                "mode": "REQUIRED"
              },
              {
                "type": "NUMERIC",
                "name": "values",
                "mode": "REPEATED"
              }
            ],
            "description": "additional numeric (not localized) attributes of the voucher",
            "type": "RECORD",
            "name": "numeric_attributes",
            "mode": "REPEATED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "type"
              },
              {
                "type": "STRING",
                "name": "name",
                "mode": "REQUIRED"
              },
              {
                "fields": [
                  {
                    "type": "STRING",
                    "name": "language",
                    "mode": "REQUIRED"
                  },
                  {
                    "type": "NUMERIC",
                    "name": "value",
                    "mode": "REQUIRED"
                  }
                ],
                "type": "RECORD",
                "name": "values",
                "mode": "REPEATED"
              }
            ],
            "description": "additional localized numeric attributes of the voucher",
            "type": "RECORD",
            "name": "localized_numeric_attributes",
            "mode": "REPEATED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "type"
              },
              {
                "type": "STRING",
                "name": "name",
                "mode": "REQUIRED"
              },
              {
                "type": "DATETIME",
                "name": "values",
                "mode": "REPEATED"
              }
            ],
            "description": "additional datetime (not localized) attributes of the voucher",
            "type": "RECORD",
            "name": "datetime_attributes",
            "mode": "REPEATED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "type"
              },
              {
                "type": "STRING",
                "name": "name",
                "mode": "REQUIRED"
              },
              {
                "fields": [
                  {
                    "type": "STRING",
                    "name": "language",
                    "mode": "REQUIRED"
                  },
                  {
                    "type": "DATETIME",
                    "name": "value",
                    "mode": "REQUIRED"
                  }
                ],
                "type": "RECORD",
                "name": "values",
                "mode": "REPEATED"
              }
            ],
            "description": "additional localized datetime attributes of the voucher",
            "type": "RECORD",
            "name": "localized_datetime_attributes",
            "mode": "REPEATED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "type"
              },
              {
                "type": "STRING",
                "name": "name",
                "mode": "REQUIRED"
              },
              {
                "type": "INTEGER",
                "name": "status",
                "mode": "REQUIRED"
              },
              {
                "fields": [
                  {
                    "fields": [
                      {
                        "type": "STRING",
                        "name": "language",
                        "mode": "REQUIRED"
                      },
                      {
                        "type": "DATETIME",
                        "name": "value",
                        "mode": "REQUIRED"
                      }
                    ],
                    "type": "RECORD",
                    "name": "start_datetime",
                    "mode": "REPEATED"
                  },
                  {
                    "fields": [
                      {
                        "type": "STRING",
                        "name": "language",
                        "mode": "REQUIRED"
                      },
                      {
                        "type": "DATETIME",
                        "name": "value",
                        "mode": "REQUIRED"
                      }
                    ],
                    "type": "RECORD",
                    "name": "end_datetime",
                    "mode": "REPEATED"
                  }
                ],
                "type": "RECORD",
                "name": "periods",
                "mode": "REPEATED"
              }
            ],
            "description": "the state of the subscriptions (e-mails and others)",
            "type": "RECORD",
            "name": "subscriptions",
            "mode": "REPEATED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "type"
              },
              {
                "type": "STRING",
                "name": "name",
                "mode": "REQUIRED"
              },
              {
                "type": "INTEGER",
                "name": "status",
                "mode": "REQUIRED"
              },
              {
                "fields": [
                  {
                    "fields": [
                      {
                        "type": "STRING",
                        "name": "language",
                        "mode": "REQUIRED"
                      },
                      {
                        "type": "DATETIME",
                        "name": "value",
                        "mode": "REQUIRED"
                      }
                    ],
                    "type": "RECORD",
                    "name": "start_datetime",
                    "mode": "REPEATED"
                  },
                  {
                    "fields": [
                      {
                        "type": "STRING",
                        "name": "language",
                        "mode": "REQUIRED"
                      },
                      {
                        "type": "DATETIME",
                        "name": "value",
                        "mode": "REQUIRED"
                      }
                    ],
                    "type": "RECORD",
                    "name": "end_datetime",
                    "mode": "REPEATED"
                  }
                ],
                "type": "RECORD",
                "name": "periods",
                "mode": "REPEATED"
              }
            ],
            "description": "the state of the notofications (e-mails and others)",
            "type": "RECORD",
            "name": "notifications",
            "mode": "REPEATED"
          },
          {
            "fields": [
              {
                "type": "STRING",
                "name": "type"
              },
              {
                "type": "STRING",
                "name": "name",
                "mode": "REQUIRED"
              },
              {
                "type": "INTEGER",
                "name": "status",
                "mode": "REQUIRED"
              },
              {
                "fields": [
                  {
                    "fields": [
                      {
                        "type": "STRING",
                        "name": "language",
                        "mode": "REQUIRED"
                      },
                      {
                        "type": "DATETIME",
                        "name": "value",
                        "mode": "REQUIRED"
                      }
                    ],
                    "type": "RECORD",
                    "name": "start_datetime",
                    "mode": "REPEATED"
                  },
                  {
                    "fields": [
                      {
                        "type": "STRING",
                        "name": "language",
                        "mode": "REQUIRED"
                      },
                      {
                        "type": "DATETIME",
                        "name": "value",
                        "mode": "REQUIRED"
                      }
                    ],
                    "type": "RECORD",
                    "name": "end_datetime",
                    "mode": "REPEATED"
                  }
                ],
                "type": "RECORD",
                "name": "periods",
                "mode": "REPEATED"
              }
            ],
            "description": "the state of the vouchers (used and others)",
            "type": "RECORD",
            "name": "voucher_states",
            "mode": "REPEATED"
          }
        ],
        "description": "billing/shipping and other contact information",
        "type": "RECORD",
        "name": "contacts",
        "mode": "REPEATED"
      },
      {
        "description": "technical field",
        "type": "DATETIME",
        "name": "creation_tm",
        "mode": "REQUIRED"
      },
      {
        "description": "technical field",
        "type": "INTEGER",
        "name": "client_id",
        "mode": "REQUIRED"
      },
      {
        "description": "technical field",
        "type": "INTEGER",
        "name": "src_sys_id",
        "mode": "REQUIRED"
      }
    ]

Expand

title	BigQuery DDL

Code Block

language	sql

CREATE OR REPLACE TABLE `YOUR_PROJECT.YOUR_DATASET.doc_user_F_20201030`   (
	persona_id STRING NOT NULL OPTIONS(description="the persona_id as referenced in other tables")
	,persona_type STRING OPTIONS(description="the type of persona (user, guest, ...)")
	,internal_id STRING OPTIONS(description="an additional internal id (optional)")
	,external_id STRING OPTIONS(description="the external identifier of the user (can be the same as the internal identifier)")
	,title STRING
	,prefix STRING
	,firstname STRING
	,middlename STRING
	,lastname STRING
	,suffix STRING
	,gender STRING
	,date_of_birth DATETIME
	,account_creation DATETIME
	,creation_label STRING
	,auto_group STRING
	,invoice_status STRING
	,status STRING
	,spouse_id STRING
	,children_ids ARRAY<STRING NOT NULL>
	,customer_groups ARRAY<STRING NOT NULL>
	, stores ARRAY<STRING> OPTIONS(description="the stores")
	, websites ARRAY<STRING> OPTIONS(description="the stores")
	,company STRING
	,vat STRING
	,vat_is_valid BOOLEAN
	,vat_request_id STRING
	,vat_request_date DATETIME
	,vat_request_success BOOLEAN
	,street STRING
	,additional_address_line STRING
	,city STRING
	,zipcode STRING
	,stateID STRING
	,department STRING
	,statename STRING
	,countryiso STRING
	,countryID STRING
	,phone STRING
	,email STRING
	,mobile_phone STRING
	,fax STRING
	,giftregistry_item_id STRING
	,string_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRING NOT NULL>>> OPTIONS(description="additional string (not localized) attributes of the voucher")
	, localized_string_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>> OPTIONS(description="additional localized string attributes of the voucher")
	, numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<NUMERIC NOT NULL>>> OPTIONS(description="additional numeric (not localized) attributes of the voucher")
	, localized_numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<language STRING NOT NULL, value NUMERIC NOT NULL>>>> OPTIONS(description="additional localized numeric attributes of the voucher")
	, datetime_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<DATETIME NOT NULL>>> OPTIONS(description="additional datetime (not localized) attributes of the voucher")
	, localized_datetime_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>> OPTIONS(description="additional localized datetime attributes of the voucher")
	, subscriptions ARRAY<STRUCT<type STRING, name STRING NOT NULL, status INT64 NOT NULL, periods ARRAY<STRUCT<start_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>, end_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>>>> OPTIONS(description="the state of the subscriptions (e-mails and others)")
	, notifications ARRAY<STRUCT<type STRING, name STRING NOT NULL, status INT64 NOT NULL, periods ARRAY<STRUCT<start_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>, end_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>>>> OPTIONS(description="the state of the notofications (e-mails and others)")
	, voucher_states ARRAY<STRUCT<type STRING, name STRING NOT NULL, status INT64 NOT NULL, periods ARRAY<STRUCT<start_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>, end_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>>>> OPTIONS(description="the state of the vouchers (used and others)")
  , contacts ARRAY<STRUCT<
		type STRING NOT NULL OPTIONS(description="e.g.: billing, shipping")
		,persona_id STRING OPTIONS(description="the persona_id as referenced in other tables")
		,persona_type STRING OPTIONS(description="the type of persona (user, guest, ...)")
		,internal_id STRING OPTIONS(description="an additional internal id (optional)")
		,external_id STRING OPTIONS(description="the external identifier of the user (can be the same as the internal identifier)")
		,title STRING
		,prefix STRING
		,firstname STRING
		,middlename STRING
		,lastname STRING
		,suffix STRING
		,gender STRING
		,date_of_birth DATETIME
		,account_creation DATETIME
		,creation_label STRING
		,auto_group STRING
		,invoice_status STRING
		,status STRING
		,spouse_id STRING
		,children_ids ARRAY<STRING NOT NULL>
		,customer_groups ARRAY<STRING NOT NULL>
		, stores ARRAY<STRING> OPTIONS(description="the stores")
		, websites ARRAY<STRING> OPTIONS(description="the stores")
		,company STRING
		,vat STRING
		,vat_is_valid BOOLEAN
		,vat_request_id STRING
		,vat_request_date DATETIME
		,vat_request_success BOOLEAN
		,street STRING
		,additional_address_line STRING
		,city STRING
		,zipcode STRING
		,stateID STRING
		,department STRING
		,statename STRING
		,countryiso STRING
		,countryID STRING
		,phone STRING
		,email STRING
		,mobile_phone STRING
		,fax STRING
		,giftregistry_item_id STRING
		,string_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRING NOT NULL>>> OPTIONS(description="additional string (not localized) attributes of the voucher")
		, localized_string_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>> OPTIONS(description="additional localized string attributes of the voucher")
		, numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<NUMERIC NOT NULL>>> OPTIONS(description="additional numeric (not localized) attributes of the voucher")
		, localized_numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<language STRING NOT NULL, value NUMERIC NOT NULL>>>> OPTIONS(description="additional localized numeric attributes of the voucher")
		, datetime_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<DATETIME NOT NULL>>> OPTIONS(description="additional datetime (not localized) attributes of the voucher")
		, localized_datetime_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>> OPTIONS(description="additional localized datetime attributes of the voucher")
		, subscriptions ARRAY<STRUCT<type STRING, name STRING NOT NULL, status INT64 NOT NULL, periods ARRAY<STRUCT<start_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>, end_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>>>> OPTIONS(description="the state of the subscriptions (e-mails and others)")
		, notifications ARRAY<STRUCT<type STRING, name STRING NOT NULL, status INT64 NOT NULL, periods ARRAY<STRUCT<start_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>, end_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>>>> OPTIONS(description="the state of the notofications (e-mails and others)")
		, voucher_states ARRAY<STRUCT<type STRING, name STRING NOT NULL, status INT64 NOT NULL, periods ARRAY<STRUCT<start_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>, end_datetime ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>>>> OPTIONS(description="the state of the vouchers (used and others)")
	>> OPTIONS(description="billing/shipping and other contact information")
  , creation_tm   DATETIME NOT NULL OPTIONS(description="technical field")
  , client_id     INT64 NOT NULL OPTIONS(description="technical field")
  , src_sys_id    INT64 NOT NULL OPTIONS(description="technical field")
  )
OPTIONS(description="Document-oriented store of users (customers) data.");{
  "persona_id": "82474",
  "prefix": "Herr",
  "firstname": "Marco",
  "lastname": "XXX",
  "street": "Amselweg 3",
  "zipcode": "4538",
  "city": "Oberbipp",
  "phone": "079947xxxx",
  "email": "xxx@hotmail.com",
  "countryID": "CH",
  "creation_tm": "2020-10-20 00:00:00",
  "client_id": 1,
  "src_sys_id": 1
}

Properties

Field name	Type	Mode	Description
*	CONTACT	REQUIRED	All the properties of the CONTACT defined type are also main parameters of the doc_user type
contacts	CONTACT	REPEATED	default contacts like for billing and shipping
creation_tm	DATETIME	REQUIRED	technical field
client_id	INTEGER	REQUIRED	technical field
src_sys_id	INTEGER	REQUIRED	technical field

Resources

BigQuery JSON Schema

https://github.com/boxalino/data-integration-doc-schema/blob/master/doc/doc_user.json

BigQuery DDL

https://github.com/boxalino/data-integration-doc-schema/blob/master/ddl/doc_user.sql

Versions Compared

Old Version 5

New Version Current

Key

Properties

Table structure

Properties

Resources

BigQuery JSON Schema

BigQuery DDL

Page Comparison

Versions Compared

Old Version 5

New Version Current

Key

Properties

Table structure

Properties

Resources

BigQuery JSON Schema

BigQuery DDL