oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#owner: jim.wjh
5
#owner group: sql2
6
#description: todo
7
--disable_warnings
8
drop database if exists createvarchar;
9
--enable_warnings
10
create database createvarchar;
11
use createvarchar;
12

13
CREATE TABLE IF NOT EXISTS rpt_chenfeng_custid_shishi_000(
14
    thedate datetime(6),
15
    puttype varchar(124),
16
    sourcetype varchar(124),
17
    clickprice varchar(124),
18
    click varchar(124),
19
    custid varchar(124),
20
    impression varchar(124),
21
    PRIMARY KEY (custid,thedate,puttype,sourcetype)
22
)  COMPRESSION='lz4_1.0' ;
23

24
CREATE TABLE IF NOT EXISTS rpt_solar_feed_bidword_ob_000(
25
    bidwordid varchar(124),
26
    searchtype varchar(124),
27
    thedate datetime(6),
28
    adgroupid varchar(124),
29
    freeclick varchar(124),
30
    directtransactionshipping varchar(124),
31
    matchscope varchar(124),
32
    memberid varchar(124),
33
    feedid varchar(124),
34
    favshoptotal varchar(124),
35
    cost varchar(124),
36
    campaignid varchar(124),
37
    favitemtotal varchar(124),
38
    indirecttransaction varchar(124),
39
    gmt_created datetime default now(),
40
    gmt_modified datetime default now(),
41
    directtransaction varchar(124),
42
    indirecttransactionshipping varchar(124),
43
    network varchar(124),
44
    invalidclick varchar(124),
45
    bidwordstr varchar(300),
46
    custid varchar(124),
47
    impression varchar(124),
48
    itemid varchar(124),
49
    click varchar(124),
50
    ranksum varchar(124),
51
    PRIMARY KEY (custid,thedate,campaignid,adgroupid,bidwordid,feedid,network,matchscope)
52
)  COMPRESSION='lz4_1.0' ;
53

54
CREATE TABLE IF NOT EXISTS rpt_chenfeng_campaignid_lixian_000(
55
    thedate datetime(6),
56
    sourcetype varchar(124),
57
    custid varchar(124),
58
    impression varchar(124),
59
    puttype varchar(124),
60
    campaignid varchar(124),
61
    clickprice varchar(124),
62
    click varchar(124),
63
    PRIMARY KEY (custid,thedate,campaignid,puttype,sourcetype)
64
)  COMPRESSION='lz4_1.0' ;
65

66
CREATE TABLE IF NOT EXISTS rpt_bpp4p_bidword_ob_000(
67
    click varchar(124),
68
    bidwordstr varchar(300),
69
    custid varchar(124),
70
    onefavshoptotal varchar(124),
71
    directtransaction varchar(124),
72
    invalidclick varchar(124),
73
    network varchar(124),
74
    campaignid varchar(124),
75
    cost varchar(124),
76
    favitemtotal varchar(124),
77
    adgroupid varchar(124),
78
    memberid varchar(124),
79
    sevenindirecttransactionshipping varchar(124),
80
    fifteenfavitemtotal varchar(124),
81
    thedate datetime(6),
82
    fifteenindirecttransactionshipping varchar(124),
83
    isshop varchar(124),
84
    fifteenfavshoptotal varchar(124),
85
    fifteendirecttransactionshipping varchar(124),
86
    threeindirecttransactionshipping varchar(124),
87
    ranksum varchar(124),
88
    sevenfavitemtotal varchar(124),
89
    impression varchar(124),
90
    threefavshoptotal varchar(124),
91
    sevenindirecttransaction varchar(124),
92
    indirecttransactionshipping varchar(124),
93
    onedirecttransactionshipping varchar(124),
94
    onefavitemtotal varchar(124),
95
    indirecttransaction varchar(124),
96
    threeindirecttransaction varchar(124),
97
    sevendirecttransactionshipping varchar(124),
98
    sevendirecttransaction varchar(124),
99
    sevenfavshoptotal varchar(124),
100
    fifteenindirecttransaction varchar(124),
101
    favshoptotal varchar(124),
102
    onedirecttransaction varchar(124),
103
    oneindirecttransactionshipping varchar(124),
104
    matchscope varchar(124),
105
    directtransactionshipping varchar(124),
106
    threefavitemtotal varchar(124),
107
    fifteendirecttransaction varchar(124),
108
    threedirecttransactionshipping varchar(124),
109
    oneindirecttransaction varchar(124),
110
    threedirecttransaction varchar(124),
111
    bidwordid varchar(124),
112
    searchtype varchar(124),
113
    PRIMARY KEY (custid,thedate,campaignid,adgroupid,bidwordid,network,matchscope)
114
)  COMPRESSION='lz4_1.0' ;
115

116
CREATE TABLE IF NOT EXISTS rpt_bpp4p_adgroupplace_name_000(
117
    placeid varchar(124),
118
    placename varchar(100),
119
    thedate datetime(6),
120
    PRIMARY KEY (placeid)
121
)  COMPRESSION='lz4_1.0' ;
122

123
CREATE TABLE IF NOT EXISTS rpt_chenfeng_custid_shishi_accumulative_000(
124
    custid varchar(124),
125
    impression varchar(124),
126
    sourcetype varchar(124),
127
    puttype varchar(124),
128
    thedate datetime(6),
129
    clickprice varchar(124),
130
    click varchar(124),
131
    PRIMARY KEY (custid,thedate,puttype,sourcetype)
132
)  COMPRESSION='lz4_1.0' ;
133

134
CREATE TABLE IF NOT EXISTS rpt_bpp4p_querysession_ob_000(
135
    relword varchar(3000),
136
    originalword varchar(300),
137
    PRIMARY KEY (originalword)
138
)  COMPRESSION='lz4_1.0' ;
139

140
CREATE TABLE IF NOT EXISTS rpt_bpp4p_area_ob_000(
141
    onedirecttransaction varchar(124),
142
    favshoptotal varchar(124),
143
    threefavitemtotal varchar(124),
144
    oneindirecttransactionshipping varchar(124),
145
    directtransactionshipping varchar(124),
146
    provinceid varchar(124),
147
    threedirecttransactionshipping varchar(124),
148
    fifteendirecttransaction varchar(124),
149
    oneindirecttransaction varchar(124),
150
    threedirecttransaction varchar(124),
151
    ext2 varchar(124),
152
    threeindirecttransactionshipping varchar(124),
153
    threefavshoptotal varchar(124),
154
    impression varchar(124),
155
    sevenfavitemtotal varchar(124),
156
    ext1 varchar(124),
157
    onedirecttransactionshipping varchar(124),
158
    indirecttransactionshipping varchar(124),
159
    onefavitemtotal varchar(124),
160
    sevenindirecttransaction varchar(124),
161
    sevenfavshoptotal varchar(124),
162
    fifteenindirecttransaction varchar(124),
163
    sevendirecttransaction varchar(124),
164
    cityid varchar(124),
165
    sevendirecttransactionshipping varchar(124),
166
    threeindirecttransaction varchar(124),
167
    indirecttransaction varchar(124),
168
    favitemtotal varchar(124),
169
    productid varchar(124),
170
    campaignid varchar(124),
171
    cost varchar(124),
172
    sevenindirecttransactionshipping varchar(124),
173
    memberid varchar(124),
174
    fifteenfavshoptotal varchar(124),
175
    fifteenfavitemtotal varchar(124),
176
    fifteenindirecttransactionshipping varchar(124),
177
    thedate datetime(6),
178
    fifteendirecttransactionshipping varchar(124),
179
    click varchar(124),
180
    traffictype varchar(124),
181
    custid varchar(124),
182
    invalidclick varchar(124),
183
    productline varchar(124),
184
    directtransaction varchar(124),
185
    onefavshoptotal varchar(124),
186
    mechanism varchar(124),
187
    PRIMARY KEY (custid,thedate,campaignid,traffictype,mechanism,provinceid,cityid,productline,productid,ext1,ext2)
188
)  COMPRESSION='lz4_1.0' ;
189

190
CREATE TABLE IF NOT EXISTS rpt_bpp4p_insight_bidword_area_000(
191
    indirecttransaction varchar(124),
192
    mechanism varchar(124),
193
    competition varchar(124),
194
    area varchar(124),
195
    indirecttransactionshipping varchar(124),
196
    network varchar(124),
197
    ext1 varchar(124),
198
    directtransaction varchar(124),
199
    bidwordstr varchar(300),
200
    impression varchar(124),
201
    click varchar(124),
202
    ext2 varchar(124),
203
    thedate datetime(6),
204
    directtransactionshipping varchar(124),
205
    bidwordhash varchar(124),
206
    cost varchar(124),
207
    favshoptotal varchar(124),
208
    favitemtotal varchar(124),
209
    PRIMARY KEY (bidwordhash,thedate,area,network,mechanism,ext1,ext2,bidwordstr)
210
)  COMPRESSION='lz4_1.0' ;
211

212
CREATE TABLE IF NOT EXISTS rpt_solar_feed_name_000(
213
    gmt_created datetime default now(),
214
    gmt_modified datetime default now(),
215
    thedate datetime(6),
216
    linkurl varchar(124),
217
    adgroupid varchar(124),
218
    custid varchar(124),
219
    title varchar(124),
220
    memberid varchar(124),
221
    imgurl varchar(124),
222
    feedid varchar(124),
223
    campaignid varchar(124),
224
    PRIMARY KEY (custid,thedate,campaignid,adgroupid,feedid)
225
)  COMPRESSION='lz4_1.0' ;
226

227
CREATE TABLE IF NOT EXISTS rpt_chenfeng_custid_lixian_accumulative_000(
228
    clickprice varchar(124),
229
    click varchar(124),
230
    sourcetype varchar(124),
231
    puttype varchar(124),
232
    thedate datetime(6),
233
    impression varchar(124),
234
    custid varchar(124),
235
    PRIMARY KEY (custid,thedate,puttype,sourcetype)
236
)  COMPRESSION='lz4_1.0' ;
237

238
CREATE TABLE IF NOT EXISTS rpt_bpp4p_creative_name_000(
239
    is_shop varchar(124),
240
    thedate datetime(6),
241
    linkurl varchar(124),
242
    adgroupid varchar(124),
243
    title varchar(124),
244
    custid varchar(124),
245
    creativeid varchar(124),
246
    memberid varchar(124),
247
    imgurl varchar(124),
248
    campaignid varchar(124),
249
    PRIMARY KEY (custid,thedate,campaignid,adgroupid,creativeid)
250
)  COMPRESSION='lz4_1.0' ;
251

252
CREATE TABLE IF NOT EXISTS rpt_bpp4p_demographic_name_000(
253
    name varchar(100),
254
    demographicid varchar(124),
255
    thedate datetime(6),
256
    PRIMARY KEY (demographicid)
257
)  COMPRESSION='lz4_1.0' ;
258

259
CREATE TABLE IF NOT EXISTS rpt_bpp4p_demographic_name_star_000(
260
    thedate datetime(6),
261
    demographicid varchar(124),
262
    name varchar(100),
263
    PRIMARY KEY (demographicid)
264
)  COMPRESSION='lz4_1.0' ;
265

266
CREATE TABLE IF NOT EXISTS rpt_solar_item_ob_000(
267
    indirecttransactionshipping varchar(124),
268
    ext1 varchar(124),
269
    onedirecttransactionshipping varchar(124),
270
    onefavitemtotal varchar(124),
271
    sevenindirecttransaction varchar(124),
272
    fifteenindirecttransaction varchar(124),
273
    sevenfavshoptotal varchar(124),
274
    productlineid varchar(124),
275
    sevendirecttransaction varchar(124),
276
    sevendirecttransactionshipping varchar(124),
277
    threeindirecttransaction varchar(124),
278
    indirecttransaction varchar(124),
279
    ranksum varchar(124),
280
    ext2 varchar(124),
281
    threeindirecttransactionshipping varchar(124),
282
    threefavshoptotal varchar(124),
283
    impression varchar(124),
284
    sevenfavitemtotal varchar(124),
285
    creativeid varchar(124),
286
    threedirecttransactionshipping varchar(124),
287
    fifteendirecttransaction varchar(124),
288
    searchtype varchar(124),
289
    rptext2 varchar(124),
290
    threedirecttransaction varchar(124),
291
    oneindirecttransaction varchar(124),
292
    onedirecttransaction varchar(124),
293
    favshoptotal varchar(124),
294
    entityid varchar(124),
295
    creativepv varchar(124),
296
    threefavitemtotal varchar(124),
297
    oneindirecttransactionshipping varchar(124),
298
    directtransactionshipping varchar(124),
299
    invalidclick varchar(124),
300
    directtransaction varchar(124),
301
    onefavshoptotal varchar(124),
302
    rptext1 varchar(124),
303
    click varchar(124),
304
    traffictype varchar(124),
305
    custid varchar(124),
306
    entitytype varchar(124),
307
    fifteenfavshoptotal varchar(124),
308
    thedate datetime(6),
309
    fifteenfavitemtotal varchar(124),
310
    fifteenindirecttransactionshipping varchar(124),
311
    fifteendirecttransactionshipping varchar(124),
312
    favitemtotal varchar(124),
313
    productid varchar(124),
314
    cost varchar(124),
315
    areaid varchar(124),
316
    campaignid varchar(124),
317
    feedid varchar(124),
318
    sevenindirecttransactionshipping varchar(124),
319
    memberid varchar(124),
320
    adgroupid varchar(124),
321
    PRIMARY KEY (memberid,thedate,productlineid,campaignid,adgroupid,creativeid,areaid,entityid,entitytype,traffictype,searchtype,productid,ext1,ext2)
322
)  COMPRESSION='lz4_1.0' ;
323

324
CREATE TABLE IF NOT EXISTS rpt_p4p_bidword_normtoorigin_000(
325
    normalword varchar(300),
326
    originalword varchar(300),
327
    PRIMARY KEY (normalword)
328
)  COMPRESSION='lz4_1.0' ;
329

330
CREATE TABLE IF NOT EXISTS rpt_chenfeng_campaignid_lixian_accumulative_000(
331
    click varchar(124),
332
    clickprice varchar(124),
333
    puttype varchar(124),
334
    campaignid varchar(124),
335
    impression varchar(124),
336
    custid varchar(124),
337
    thedate datetime(6),
338
    sourcetype varchar(124),
339
    PRIMARY KEY (custid,thedate,campaignid,puttype,sourcetype)
340
)  COMPRESSION='lz4_1.0' ;
341

342
CREATE TABLE IF NOT EXISTS rpt_chenfeng_custid_lixian_000(
343
    custid varchar(124),
344
    impression varchar(124),
345
    sourcetype varchar(124),
346
    puttype varchar(124),
347
    thedate datetime(6),
348
    click varchar(124),
349
    clickprice varchar(124),
350
    PRIMARY KEY (custid,thedate,puttype,sourcetype)
351
)  COMPRESSION='lz4_1.0' ;
352

353
CREATE TABLE IF NOT EXISTS rpt_chenfeng_campaignid_shishi_000(
354
    thedate datetime(6),
355
    sourcetype varchar(124),
356
    custid varchar(124),
357
    impression varchar(124),
358
    campaignid varchar(124),
359
    puttype varchar(124),
360
    click varchar(124),
361
    clickprice varchar(124),
362
    PRIMARY KEY (custid,thedate,campaignid,puttype,sourcetype)
363
)  COMPRESSION='lz4_1.0' ;
364

365
CREATE TABLE IF NOT EXISTS rpt_chenfeng_campaignid_shishi_accumulative_000(
366
    clickprice varchar(124),
367
    click varchar(124),
368
    puttype varchar(124),
369
    campaignid varchar(124),
370
    impression varchar(124),
371
    custid varchar(124),
372
    sourcetype varchar(124),
373
    thedate datetime(6),
374
    PRIMARY KEY (custid,thedate,campaignid,puttype,sourcetype)
375
)  COMPRESSION='lz4_1.0' ;
376

377
CREATE TABLE IF NOT EXISTS rpt_bpp4p_custom_ob_000(
378
    oneindirecttransaction varchar(124),
379
    threedirecttransaction varchar(124),
380
    fifteendirecttransaction varchar(124),
381
    threedirecttransactionshipping varchar(124),
382
    directtransactionshipping varchar(124),
383
    oneindirecttransactionshipping varchar(124),
384
    threefavitemtotal varchar(124),
385
    favshoptotal varchar(124),
386
    onedirecttransaction varchar(124),
387
    indirecttransaction varchar(124),
388
    threeindirecttransaction varchar(124),
389
    sevendirecttransactionshipping varchar(124),
390
    sevendirecttransaction varchar(124),
391
    sevenfavshoptotal varchar(124),
392
    fifteenindirecttransaction varchar(124),
393
    sevenindirecttransaction varchar(124),
394
    onedirecttransactionshipping varchar(124),
395
    indirecttransactionshipping varchar(124),
396
    onefavitemtotal varchar(124),
397
    sevenfavitemtotal varchar(124),
398
    impression varchar(124),
399
    threefavshoptotal varchar(124),
400
    threeindirecttransactionshipping varchar(124),
401
    ranksum varchar(124),
402
    fifteendirecttransactionshipping varchar(124),
403
    fifteenfavitemtotal varchar(124),
404
    fifteenindirecttransactionshipping varchar(124),
405
    thedate datetime(6),
406
    fifteenfavshoptotal varchar(124),
407
    memberid varchar(124),
408
    sevenindirecttransactionshipping varchar(124),
409
    cost varchar(124),
410
    favitemtotal varchar(124),
411
    onefavshoptotal varchar(124),
412
    directtransaction varchar(124),
413
    invalidclick varchar(124),
414
    custid varchar(124),
415
    click varchar(124),
416
    PRIMARY KEY (custid,thedate)
417
)  COMPRESSION='lz4_1.0' ;
418

419
CREATE TABLE IF NOT EXISTS rpt_bpp4p_demographic_name_purchase_000(
420
    demographicid varchar(124),
421
    thedate datetime(6),
422
    name varchar(100),
423
    PRIMARY KEY (demographicid)
424
)  COMPRESSION='lz4_1.0' ;
425

426
CREATE TABLE IF NOT EXISTS rpt_bpp4p_tag_000(
427
    name varchar(300),
428
    gmt_created datetime default now(),
429
    id varchar(124),
430
    type varchar(124),
431
    gmt_modified datetime default now(),
432
    PRIMARY KEY (id)
433
)  COMPRESSION='lz4_1.0' ;
434

435
CREATE TABLE IF NOT EXISTS rpt_bpp4p_adgroup_ob_000(
436
    adgroupid varchar(124),
437
    sevenindirecttransactionshipping varchar(124),
438
    memberid varchar(124),
439
    cost varchar(124),
440
    campaignid varchar(124),
441
    favitemtotal varchar(124),
442
    fifteendirecttransactionshipping varchar(124),
443
    fifteenindirecttransactionshipping varchar(124),
444
    fifteenfavitemtotal varchar(124),
445
    thedate datetime(6),
446
    isshop varchar(124),
447
    fifteenfavshoptotal varchar(124),
448
    custid varchar(124),
449
    click varchar(124),
450
    onefavshoptotal varchar(124),
451
    invalidclick varchar(124),
452
    network varchar(124),
453
    directtransaction varchar(124),
454
    oneindirecttransactionshipping varchar(124),
455
    directtransactionshipping varchar(124),
456
    matchscope varchar(124),
457
    threefavitemtotal varchar(124),
458
    favshoptotal varchar(124),
459
    onedirecttransaction varchar(124),
460
    threedirecttransaction varchar(124),
461
    oneindirecttransaction varchar(124),
462
    searchtype varchar(124),
463
    auctionid varchar(124),
464
    fifteendirecttransaction varchar(124),
465
    threedirecttransactionshipping varchar(124),
466
    sevenfavitemtotal varchar(124),
467
    creativeid varchar(124),
468
    threefavshoptotal varchar(124),
469
    impression varchar(124),
470
    threeindirecttransactionshipping varchar(124),
471
    ranksum varchar(124),
472
    sevendirecttransactionshipping varchar(124),
473
    threeindirecttransaction varchar(124),
474
    indirecttransaction varchar(124),
475
    fifteenindirecttransaction varchar(124),
476
    sevenfavshoptotal varchar(124),
477
    sevendirecttransaction varchar(124),
478
    onefavitemtotal varchar(124),
479
    indirecttransactionshipping varchar(124),
480
    onedirecttransactionshipping varchar(124),
481
    sevenindirecttransaction varchar(124),
482
    PRIMARY KEY (custid,thedate,campaignid,adgroupid,creativeid,network,matchscope,searchtype)
483
)  COMPRESSION='lz4_1.0' ;
484

485
CREATE TABLE IF NOT EXISTS rpt_bpp4p_dim_000(
486
    gmt_modified datetime default now(),
487
    id varchar(124),
488
    name varchar(300),
489
    gmt_created datetime default now(),
490
    PRIMARY KEY (id)
491
)  COMPRESSION='lz4_1.0' ;
492

493
CREATE TABLE IF NOT EXISTS rpt_bpp4p_adgroup_name_000(
494
    title varchar(124),
495
    custid varchar(124),
496
    adgroupid varchar(124),
497
    memberid varchar(124),
498
    campaignid varchar(124),
499
    imgurl varchar(124),
500
    auctionid varchar(124),
501
    thedate datetime(6),
502
    isshop varchar(124),
503
    linkurl varchar(124),
504
    PRIMARY KEY (custid,thedate,campaignid,adgroupid)
505
)  COMPRESSION='lz4_1.0' ;
506

507
CREATE TABLE IF NOT EXISTS rpt_bpp4p_insight_bidword_price_000(
508
    indirecttransaction varchar(124),
509
    competition varchar(124),
510
    mechanism varchar(124),
511
    directtransaction varchar(124),
512
    ext1 varchar(124),
513
    indirecttransactionshipping varchar(124),
514
    network varchar(124),
515
    bidwordstr varchar(300),
516
    impression varchar(124),
517
    ext2 varchar(124),
518
    click varchar(124),
519
    pricedistribution varchar(124),
520
    thedate datetime(6),
521
    directtransactionshipping varchar(124),
522
    bidwordhash varchar(124),
523
    favshoptotal varchar(124),
524
    cost varchar(124),
525
    favitemtotal varchar(124),
526
    PRIMARY KEY (bidwordhash,thedate,pricedistribution,network,mechanism,ext1,ext2,bidwordstr)
527
)  COMPRESSION='lz4_1.0' ;
528

529
CREATE TABLE IF NOT EXISTS rpt_bpp4p_sexpowerstar_ob_000(
530
    directtransaction varchar(124),
531
    invalidclick varchar(124),
532
    onefavshoptotal varchar(124),
533
    click varchar(124),
534
    custid varchar(124),
535
    fifteenfavshoptotal varchar(124),
536
    fifteenfavitemtotal varchar(124),
537
    thedate datetime(6),
538
    fifteenindirecttransactionshipping varchar(124),
539
    fifteendirecttransactionshipping varchar(124),
540
    demographicstarid varchar(124),
541
    demographicsexid varchar(124),
542
    favitemtotal varchar(124),
543
    campaignid varchar(124),
544
    cost varchar(124),
545
    memberid varchar(124),
546
    sevenindirecttransactionshipping varchar(124),
547
    sevenindirecttransaction varchar(124),
548
    onefavitemtotal varchar(124),
549
    indirecttransactionshipping varchar(124),
550
    onedirecttransactionshipping varchar(124),
551
    sevendirecttransaction varchar(124),
552
    fifteenindirecttransaction varchar(124),
553
    sevenfavshoptotal varchar(124),
554
    indirecttransaction varchar(124),
555
    threeindirecttransaction varchar(124),
556
    sevendirecttransactionshipping varchar(124),
557
    threeindirecttransactionshipping varchar(124),
558
    impression varchar(124),
559
    threefavshoptotal varchar(124),
560
    sevenfavitemtotal varchar(124),
561
    threedirecttransactionshipping varchar(124),
562
    fifteendirecttransaction varchar(124),
563
    demographicpurchaseid varchar(124),
564
    threedirecttransaction varchar(124),
565
    oneindirecttransaction varchar(124),
566
    onedirecttransaction varchar(124),
567
    favshoptotal varchar(124),
568
    threefavitemtotal varchar(124),
569
    directtransactionshipping varchar(124),
570
    oneindirecttransactionshipping varchar(124),
571
    PRIMARY KEY (custid,thedate,campaignid,demographicsexid,demographicpurchaseid,demographicstarid)
572
)  COMPRESSION='lz4_1.0' ;
573

574
CREATE TABLE IF NOT EXISTS rpt_bpp4p_campaign_name_000(
575
    memeberid varchar(124),
576
    thedate datetime(6),
577
    campaignid varchar(124),
578
    campaigintitle varchar(124),
579
    custid varchar(124),
580
    campaigntype varchar(124),
581
    PRIMARY KEY (custid,thedate,campaignid)
582
)  COMPRESSION='lz4_1.0' ;
583

584
CREATE TABLE IF NOT EXISTS rpt_bpp4p_insight_bidword_000(
585
    mechanism varchar(124),
586
    competition varchar(124),
587
    indirecttransaction varchar(124),
588
    ext1 varchar(124),
589
    indirecttransactionshipping varchar(124),
590
    network varchar(124),
591
    directtransaction varchar(124),
592
    impression varchar(124),
593
    bidwordstr varchar(512),
594
    click varchar(124),
595
    ext2 varchar(124),
596
    thedate datetime(6),
597
    bidwordhash varchar(124),
598
    directtransactionshipping varchar(124),
599
    favitemtotal varchar(124),
600
    cost varchar(124),
601
    favshoptotal varchar(124),
602
    PRIMARY KEY (bidwordhash,thedate,network,mechanism,ext1,ext2,bidwordstr)
603
)  COMPRESSION='lz4_1.0' ;
604

605
CREATE TABLE IF NOT EXISTS rpt_bpp4p_insight_bidword_category_000(
606
    click varchar(124),
607
    ext2 varchar(124),
608
    bidwordstr varchar(300),
609
    impression varchar(124),
610
    indirecttransactionshipping varchar(124),
611
    ext1 varchar(124),
612
    network varchar(124),
613
    directtransaction varchar(124),
614
    indirecttransaction varchar(124),
615
    mechanism varchar(124),
616
    competition varchar(124),
617
    favshoptotal varchar(124),
618
    cost varchar(124),
619
    favitemtotal varchar(124),
620
    directtransactionshipping varchar(124),
621
    bidwordhash varchar(124),
622
    thedate datetime(6),
623
    category varchar(124),
624
    PRIMARY KEY (category,bidwordhash,thedate,network,mechanism,ext1,ext2,bidwordstr)
625
)  COMPRESSION='lz4_1.0' ;
626

627
CREATE TABLE IF NOT EXISTS rpt_bpp4p_demographic_name_sex_000(
628
    name varchar(100),
629
    thedate datetime(6),
630
    demographicid varchar(124),
631
    PRIMARY KEY (demographicid)
632
)  COMPRESSION='lz4_1.0' ;
633

634
CREATE TABLE IF NOT EXISTS rpt_bpp4p_pos_ob_000(
635
    threedirecttransactionshipping varchar(124),
636
    fifteendirecttransaction varchar(124),
637
    auctionid varchar(124),
638
    oneindirecttransaction varchar(124),
639
    threedirecttransaction varchar(124),
640
    dimid varchar(124),
641
    onedirecttransaction varchar(124),
642
    favshoptotal varchar(124),
643
    threefavitemtotal varchar(124),
644
    oneindirecttransactionshipping varchar(124),
645
    directtransactionshipping varchar(124),
646
    sevenindirecttransaction varchar(124),
647
    onedirecttransactionshipping varchar(124),
648
    indirecttransactionshipping varchar(124),
649
    onefavitemtotal varchar(124),
650
    sevendirecttransaction varchar(124),
651
    fifteenindirecttransaction varchar(124),
652
    sevenfavshoptotal varchar(124),
653
    indirecttransaction varchar(124),
654
    threeindirecttransaction varchar(124),
655
    sevendirecttransactionshipping varchar(124),
656
    threeindirecttransactionshipping varchar(124),
657
    impression varchar(124),
658
    threefavshoptotal varchar(124),
659
    placeid varchar(124),
660
    sevenfavitemtotal varchar(124),
661
    fifteenfavshoptotal varchar(124),
662
    isshop varchar(124),
663
    fifteenindirecttransactionshipping varchar(124),
664
    fifteenfavitemtotal varchar(124),
665
    thedate datetime(6),
666
    fifteendirecttransactionshipping varchar(124),
667
    favitemtotal varchar(124),
668
    cost varchar(124),
669
    campaignid varchar(124),
670
    memberid varchar(124),
671
    sevenindirecttransactionshipping varchar(124),
672
    adgroupid varchar(124),
673
    directtransaction varchar(124),
674
    invalidclick varchar(124),
675
    onefavshoptotal varchar(124),
676
    click varchar(124),
677
    custid varchar(124),
678
    PRIMARY KEY (custid,thedate,campaignid,adgroupid,placeid)
679
)  COMPRESSION='lz4_1.0' ;
680

681
CREATE TABLE IF NOT EXISTS rpt_bpp4p_campaign_ob_000(
682
    searchtype varchar(124),
683
    oneindirecttransaction varchar(124),
684
    threedirecttransaction varchar(124),
685
    threedirecttransactionshipping varchar(124),
686
    fifteendirecttransaction varchar(124),
687
    threefavitemtotal varchar(124),
688
    matchscope varchar(124),
689
    directtransactionshipping varchar(124),
690
    oneindirecttransactionshipping varchar(124),
691
    onedirecttransaction varchar(124),
692
    favshoptotal varchar(124),
693
    sevenfavshoptotal varchar(124),
694
    fifteenindirecttransaction varchar(124),
695
    sevendirecttransaction varchar(124),
696
    sevendirecttransactionshipping varchar(124),
697
    threeindirecttransaction varchar(124),
698
    indirecttransaction varchar(124),
699
    onefavitemtotal varchar(124),
700
    indirecttransactionshipping varchar(124),
701
    onedirecttransactionshipping varchar(124),
702
    sevenindirecttransaction varchar(124),
703
    threefavshoptotal varchar(124),
704
    impression varchar(124),
705
    sevenfavitemtotal varchar(124),
706
    ranksum varchar(124),
707
    threeindirecttransactionshipping varchar(124),
708
    fifteendirecttransactionshipping varchar(124),
709
    fifteenfavshoptotal varchar(124),
710
    isshop varchar(124),
711
    fifteenfavitemtotal varchar(124),
712
    fifteenindirecttransactionshipping varchar(124),
713
    thedate datetime(6),
714
    sevenindirecttransactionshipping varchar(124),
715
    memberid varchar(124),
716
    favitemtotal varchar(124),
717
    campaignid varchar(124),
718
    cost varchar(124),
719
    invalidclick varchar(124),
720
    network varchar(124),
721
    directtransaction varchar(124),
722
    onefavshoptotal varchar(124),
723
    custid varchar(124),
724
    click varchar(124),
725
    PRIMARY KEY (custid,thedate,campaignid,network,matchscope,searchtype,isshop)
726
)  COMPRESSION='lz4_1.0' ;
727

728
CREATE TABLE IF NOT EXISTS rpt_bpp4p_area_name_000(
729
    cityid varchar(124),
730
    cityname varchar(100),
731
    provinceid varchar(124),
732
    provincename varchar(100),
733
    PRIMARY KEY (provinceid,cityid)
734
)  COMPRESSION='lz4_1.0' ;
735

736
set @@recyclebin = off;
737
drop database createvarchar;
738