-
Notifications
You must be signed in to change notification settings - Fork 0
/
doc_attribute_value.sql
30 lines (30 loc) · 5.25 KB
/
doc_attribute_value.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE OR REPLACE TABLE `{{ YOUR_PROJECT }}.{{ YOUR_DATASET }}.doc_attribute_value_{{ F|D }}_{{ YYYYMMDD }}` (
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>>>>>> 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 ARRAY<STRUCT<type STRING, values ARRAY<STRUCT<label STRING, language STRING, value STRING NOT NULL, currency STRING, region STRING>>>> OPTIONS(description="pricing information about the attribute value, e.g.: STRUCT('de','discount','Bis:', '-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>>>>>> OPTIONS(description="additional localized string attributes of the attribute value")
, numeric_attributes ARRAY<STRUCT<type STRING, name STRING NOT NULL, key STRING, 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, key STRING, values ARRAY<STRUCT<value_id STRING, value ARRAY<STRUCT<language STRING NOT NULL, value NUMERIC NOT 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>>>>>> 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");