CREATE OR REPLACE TABLE `YOUR_PROJECT.YOUR_DATASET.doc_attribute_value_F_20201030` (
attribute_name STRING NOT NULL OPTIONS(description="the name of the attribute (should match the name of the attribute provided in the doc_product table in either string_attributes, numeric_attributes or localized_numeric_attributes")
, numerical BOOLEAN OPTIONS(description="are the provided attribute value identifiers numerical? fyi - in this tables, the values are always provided as strings, but not in the doc_products and other tables")
, value_id STRING NOT NULL OPTIONS(description="the value id to map to it's related value labels (always a string, but must be castable to numeric if numerical = true)")
, value_label ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the labels of the value id")
, parent_value_ids ARRAY<STRING NOT NULL> OPTIONS(description="the value ids of the parent (only for hierarchical attributes), (always a string, but must be castable to numeric if numerical = true)")
, products ARRAY<STRUCT<type STRING, name STRING, product_line STRING, product_group STRING, sku STRING, value NUMERIC>> OPTIONS(description="connections to products (normally not needed, as should be provided in the doc_product table")
, stores ARRAY<STRING> OPTIONS(description="the stores")
, title ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the title of the attribute value")
, short_description ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the short description of the attribute value")
, description ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>> OPTIONS(description="the description of the attribute value")
, images ARRAY<STRUCT<name STRING, values ARRAY<STRUCT<value_id STRING, value ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>>NULL>>>>>> OPTIONS(description="the images of the attribute value")
, link ARRAY<STRUCT<language STRING, value STRING NOT NULL>> OPTIONS(description="the link of the attribute value")
, tags ARRAY<STRUCT<type STRING, value STRING, loc_values ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>> OPTIONS(description="the tags of the attribute value, e.g.: [STRUCT('tag', 'hello world', [STRUCT('de', 'hello world')])]")
, labels ARRAY<STRUCT<type STRING, name STRING NOT NULL, value STRING, loc_values ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>> OPTIONS(description="the labels of the attribute value, e.g.: [STRUCT('symbol', 'delivery', '24h', [STRUCT('de', '24-H Versand')])]")
, pricing STRUCT<typeARRAY<STRUCT<type STRING NOT NULL, labelvalues ARRAY<STRUCT<language STRING NOT NULL, valuelabel STRING NOT NULL>>, value ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>, sign ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>sign STRING>>>> OPTIONS(description="pricing information about the attribute value, e.g.: STRUCT('discountde',[STRUCT('dediscount','Bis:')],[STRUCT('de','-50:')],[STRUCT('de','%')]), '-50.-', 'CHF'")
, visibility ARRAY<STRUCT<customer_groups ARRAY<STRING NOT NULL>, values ARRAY<STRUCT<language STRING NOT NULL, value INT64 NOT NULL>>>> OPTIONS(description="the attribute value visibility :VISIBILITY_NOT_VISIBLE = 1; VISIBILITY_IN_CATALOG = 2; VISIBILITY_IN_SEARCH = 3; VISIBILITY_BOTH = 4;")
, status ARRAY<STRUCT<language STRING NOT NULL, value INT64 NOT NULL>> OPTIONS(description="the attribute value status")
, 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="information about the activity periods of the attribute value")
, string_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRING NOT NULL>>> OPTIONS(description="additional string (not localized) attributes of the attribute value")
, localized_string_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<value_id STRING, value ARRAY<STRUCT<language STRING NOT NULL, value STRING NOT NULL>>>>NULL>>>>>> OPTIONS(description="additional localized string attributes of the attribute value")
, numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<NUMERIC NOT NULL>>> OPTIONS(description="additional numeric (not localized) attributes of the attribute value")
, localized_numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<value_id STRING, value ARRAY<STRUCT<language STRING NOT NULL, value NUMERIC NOT NULL>>>>NULL>>>>>> OPTIONS(description="additional localized numeric attributes of the attribute value")
, datetime_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<DATETIME NOT NULL>>> OPTIONS(description="additional datetime (not localized) attributes of the attribute value")
, localized_datetime_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, values ARRAY<STRUCT<value_id STRING, value ARRAY<STRUCT<language STRING NOT NULL, value DATETIME NOT NULL>>>>NULL>>>>>> OPTIONS(description="additional localized datetime attributes of the attribute value")
, 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="The attribute values to map string or numeric attributes to (possibly hierarchical) localized attributes"); |