-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathpgloader_conf
120 lines (118 loc) · 3.44 KB
/
pgloader_conf
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
112
113
114
115
116
117
118
119
120
LOAD CSV
from inline
HAVING FIELDS
(
log_time,
user_name ,
database_name ,
process_id ,
connection_from ,
session_id ,
session_line_num ,
command_tag ,
session_start_time ,
virtual_transaction_id ,
transaction_id ,
error_severity ,
sql_state_code ,
message ,
detail ,
hint ,
internal_query ,
internal_query_pos ,
context ,
query ,
query_pos ,
location ,
application_name
)
into postgresql://localhost:30004/postgres_log?postgres_log
target columns
(
log_time,
user_name ,
database_name ,
process_id ,
connection_from ,
session_id ,
session_line_num ,
command_tag ,
session_start_time ,
virtual_transaction_id ,
transaction_id ,
error_severity ,
sql_state_code ,
message ,
detail ,
hint ,
internal_query ,
internal_query_pos ,
context ,
query ,
query_pos ,
location ,
application_name
)
WITH truncate,
fields optionally enclosed by '"',
fields terminated by ',',
batch rows = 100,
batch size = 8 MB;
2014-11-07 10:02:17.302 CST,"xx_new","xx_insight_new",18445,"10.136.165.93:39143",545c1a19.480d,1,"COPY",2014-11-07 09:02:17 CST,6/1303255,1197337058,ERROR,57014,"canceling statement due to statement timeout",,,,,,"COPY (select t1.fspace, t3.fname, fad_target_classid, t2.name, num_client, num_client_big_imp, coverage
from
(select
client_cnt.fspace, client_cnt.fad_target_classid, num_client, num_client_big_imp, case when num_client > 0 then num_client_big_imp * 1.0/ num_client else 0 end as coverage
from (
select
fspace,fad_target_classid, count(distinct adinfo.fad_advertiser_id) as num_client
from
(select
fspace, fad
from
xx_ad_r_space_day
where fdatetime='20141106') ad
left join (
select
fad_id, fad_target_classid, fad_advertiser_id
from
xx_ad_info_day
where
fdatetime='20141106'
group by fad_id, fad_target_classid, fad_advertiser_id
) adinfo
on
ad.fad = adinfo.fad_id
group by fspace,fad_target_classid
) client_cnt
left join
(select fspace, fad_target_classid, count(distinct client_big_imp) as num_client_big_imp
from
(select
cs.fspace, fad_target_classid, fad_advertiser_id as client_big_imp
from
(select
fspace, fad, fimpression
from xx_ctr_space_alg_ad_day
where fdatetime='20141106') cs
left join
(select
fad_id,fad_target_classid, fad_advertiser_id
from xx_ad_info_day
where fdatetime='20141106'
group by fad_id,fad_target_classid, fad_advertiser_id
) ad
on (cs.fad = ad.fad_id)
group by fspace,fad_target_classid, fad_advertiser_id
having sum(cs.fimpression) > 10000
) big_imp
group by fspace, fad_target_classid
) client_cnt_big_imp
on (client_cnt.fspace = client_cnt_big_imp.fspace and client_cnt.fad_target_classid = client_cnt_big_imp.fad_target_classid)
) t1
left join
(select id, name from xx_ad_source_info ) t2
on (t1.fad_target_classid = t2.id)
left join
(select fspace, fname from xx_space_new_info) t3
on (t1.fspace = t3.fspace)
order by t1.fspace, t1.fad_target_classid) TO STDOUT DELIMITER ',' NULL 'null' CSV QUOTE '""'",,"ProcessInterrupts, postgres.c:3314","psql"