e4sXZKzr

1CREATE TABLE IF NOT EXISTS `mvideo-1539598073758.bytedance.test` 
2(
3  id string,
4  register_time INT64,
5  membership_level STRING,
6  country STRING,
7  province STRING,
8  city string,
9  extra_info struct <
10    user_pseudo_id string,
11    category string,
12    mobile_brand_name string,
13    mobile_model_name string,
14    mobile_os_hardware_model string,
15    operating_system string,
16    operating_system_version string,
17    vendor_id string
18  >
19);
20
21INSERT INTO `mvideo-1539598073758.bytedance.test`
22SELECT
23  id,
24  register_time,
25  membership_level,
26  country,
27  province,
28  city,
29  (
30    SELECT AS STRUCT
31      user_pseudo_id,
32      category,
33      mobile_brand_name,
34      mobile_model_name,
35      mobile_os_hardware_model,
36      operating_system,
37      operating_system_version,
38      vendor_id 
39  ) AS extra_info
40FROM 
41(
42  SELECT DISTINCT 
43    user_id AS id,
44    user_pseudo_id,
45
46    geo.country AS country,
47    geo.region AS province,
48    geo.city AS city,
49
50    device.category,
51    device.mobile_brand_name,
52    device.mobile_model_name,
53    device.mobile_os_hardware_model,
54    device.operating_system,
55    device.operating_system_version,
56    device.vendor_id,
57
58    user_first_touch_timestamp AS register_time,
59    (
60      SELECT
61        value.string_value
62      FROM
63        UNNEST(user_properties)
64      WHERE
65        key = ‘user_auth’
66    ) AS membership_level
67  FROM `mvideo-1539598073758.analytics_234497047.events_*`
68)