-
Notifications
You must be signed in to change notification settings - Fork 0
/
doc_user.sql
111 lines (111 loc) · 7.58 KB
/
doc_user.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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
CREATE OR REPLACE TABLE `{{ YOUR_PROJECT }}.{{ YOUR_DATASET }}.doc_user_{{ F|D }}_{{ YYYYMMDD }}` (
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, key STRING, 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, key STRING, 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, key STRING, 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, key STRING, 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.");