質問編集履歴
6
質問更新
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -8,16 +8,10 @@
|
|
|
8
8
|
|
|
9
9
|
➀OracleDBのデータをembulkでBigQueryへバルクインサートする。対象のテーブルは２つ(A,B)
|
|
10
10
|
|
|
11
|
-
|
|
12
|
-
|
|
13
11
|
➁BigQueryに作成された2つのテーブル（A,B）、別途BigQueryにテーブルを１つ(C)作成する
|
|
14
12
|
|
|
15
|
-
|
|
16
|
-
|
|
17
13
|
➂2つのテーブル(A,B)から、sqlを使って特定条件のもと結合処理を行い、必要なレコードだけをCテーブルへインサートする
|
|
18
14
|
|
|
19
|
-
|
|
20
|
-
|
|
21
15
|
CronJobを使って、➂の処理を定期実行（1分間隔で実行）させたい。
|
|
22
16
|
|
|
23
17
|
|
|
@@ -28,10 +22,6 @@
|
|
|
28
22
|
|
|
29
23
|
|
|
30
24
|
|
|
31
|
-
１と２について調査中となります。
|
|
32
|
-
|
|
33
|
-
|
|
34
|
-
|
|
35
25
|
※2018/12/21
|
|
36
26
|
|
|
37
27
|
```ここに言語を入力
|
|
@@ -42,8 +32,6 @@
|
|
|
42
32
|
|
|
43
33
|
```
|
|
44
34
|
|
|
45
|
-
|
|
46
|
-
|
|
47
35
|
※2018/12/25
|
|
48
36
|
|
|
49
37
|
```
|
|
@@ -52,8 +40,364 @@
|
|
|
52
40
|
|
|
53
41
|
Schedule queryでは、実行間隔の最短許容時間は 3 時間となる
|
|
54
42
|
|
|
55
|
-
|
|
56
|
-
|
|
57
43
|
⇒bigqueryでSQLを1分間隔で定期実行する方法を知りたいです
|
|
58
44
|
|
|
59
45
|
```
|
|
46
|
+
|
|
47
|
+
※2018/12/25
|
|
48
|
+
|
|
49
|
+
```ここに言語を入力
|
|
50
|
+
|
|
51
|
+
※※実行させたいクエリ※※
|
|
52
|
+
|
|
53
|
+
|
|
54
|
+
|
|
55
|
+
dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$ cat bigquery_tbl_gousei.sql
|
|
56
|
+
|
|
57
|
+
|
|
58
|
+
|
|
59
|
+
insert into `fluid-emissary-216806.embulk_test.INPUT_TEST_003` (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9)
|
|
60
|
+
|
|
61
|
+
select * from `fluid-emissary-216806.embulk_test.INPUT_TEST_001`
|
|
62
|
+
|
|
63
|
+
where Num In
|
|
64
|
+
|
|
65
|
+
(select Num from `fluid-emissary-216806.embulk_test.INPUT_TEST_002` A
|
|
66
|
+
|
|
67
|
+
where not exists(select 1 from `fluid-emissary-216806.embulk_test.INPUT_TEST_003` B where A.NUM=B.NUM));
|
|
68
|
+
|
|
69
|
+
|
|
70
|
+
|
|
71
|
+
commit;
|
|
72
|
+
|
|
73
|
+
|
|
74
|
+
|
|
75
|
+
|
|
76
|
+
|
|
77
|
+
※※クーロン定義※※
|
|
78
|
+
|
|
79
|
+
|
|
80
|
+
|
|
81
|
+
dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$ cat cron.yml
|
|
82
|
+
|
|
83
|
+
cron:
|
|
84
|
+
|
|
85
|
+
- description: "test job"
|
|
86
|
+
|
|
87
|
+
url: /home/dwhtest01/cron/bigquery_tbl_gousei.sql
|
|
88
|
+
|
|
89
|
+
schedule: every 1 mins
|
|
90
|
+
|
|
91
|
+
dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$
|
|
92
|
+
|
|
93
|
+
dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$
|
|
94
|
+
|
|
95
|
+
|
|
96
|
+
|
|
97
|
+
|
|
98
|
+
|
|
99
|
+
※※デプロイ※※
|
|
100
|
+
|
|
101
|
+
|
|
102
|
+
|
|
103
|
+
dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$ gcloud app deploy cron.yml
|
|
104
|
+
|
|
105
|
+
Configurations to update:
|
|
106
|
+
|
|
107
|
+
|
|
108
|
+
|
|
109
|
+
descriptor: [/home/dwhtest01/cron/cron.yml]
|
|
110
|
+
|
|
111
|
+
type: [cron jobs]
|
|
112
|
+
|
|
113
|
+
target project: [fluid-emissary-216806]
|
|
114
|
+
|
|
115
|
+
|
|
116
|
+
|
|
117
|
+
|
|
118
|
+
|
|
119
|
+
Do you want to continue (Y/n)? y
|
|
120
|
+
|
|
121
|
+
|
|
122
|
+
|
|
123
|
+
Updating config [cron]...done.
|
|
124
|
+
|
|
125
|
+
|
|
126
|
+
|
|
127
|
+
Cron jobs have been updated.
|
|
128
|
+
|
|
129
|
+
|
|
130
|
+
|
|
131
|
+
Visit the Cloud Platform Console Task Queues page to view your queues and cron jobs.
|
|
132
|
+
|
|
133
|
+
https://console.cloud.google.com/appengine/taskqueues/cron?project=fluid-emissary-216806
|
|
134
|
+
|
|
135
|
+
|
|
136
|
+
|
|
137
|
+
dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$
|
|
138
|
+
|
|
139
|
+
|
|
140
|
+
|
|
141
|
+
|
|
142
|
+
|
|
143
|
+
|
|
144
|
+
|
|
145
|
+
※※エラーログ※※
|
|
146
|
+
|
|
147
|
+
|
|
148
|
+
|
|
149
|
+
[
|
|
150
|
+
|
|
151
|
+
{
|
|
152
|
+
|
|
153
|
+
"protoPayload": {
|
|
154
|
+
|
|
155
|
+
"@type": "type.googleapis.com/google.appengine.logging.v1.RequestLog",
|
|
156
|
+
|
|
157
|
+
"appId": "b~fluid-emissary-216806",
|
|
158
|
+
|
|
159
|
+
"versionId": "20181213t142210",
|
|
160
|
+
|
|
161
|
+
"requestId": "5c21fa7800ff0ca50930d01c390001627e666c7569642d656d6973736172792d3231363830360001323031383132313374313432323130000100",
|
|
162
|
+
|
|
163
|
+
"ip": "0.1.0.1",
|
|
164
|
+
|
|
165
|
+
"startTime": "2018-12-25T09:38:00.828681Z",
|
|
166
|
+
|
|
167
|
+
"endTime": "2018-12-25T09:38:00.836503Z",
|
|
168
|
+
|
|
169
|
+
"latency": "0.007822s",
|
|
170
|
+
|
|
171
|
+
"megaCycles": "18",
|
|
172
|
+
|
|
173
|
+
"method": "GET",
|
|
174
|
+
|
|
175
|
+
"resource": "/home/dwhtest01/cron/bigquery_tbl_gousei.sql",
|
|
176
|
+
|
|
177
|
+
"httpVersion": "HTTP/1.1",
|
|
178
|
+
|
|
179
|
+
"status": 404,
|
|
180
|
+
|
|
181
|
+
"responseSize": "270",
|
|
182
|
+
|
|
183
|
+
"userAgent": "AppEngine-Google; (+http://code.google.com/appengine)",
|
|
184
|
+
|
|
185
|
+
"urlMapEntry": "unused",
|
|
186
|
+
|
|
187
|
+
"host": "fluid-emissary-216806.appspot.com",
|
|
188
|
+
|
|
189
|
+
"cost": 3.9227e-8,
|
|
190
|
+
|
|
191
|
+
"taskQueueName": "__cron",
|
|
192
|
+
|
|
193
|
+
"taskName": "628d389511e5aca63075275b587e7723",
|
|
194
|
+
|
|
195
|
+
"instanceIndex": -1,
|
|
196
|
+
|
|
197
|
+
"finished": true,
|
|
198
|
+
|
|
199
|
+
"instanceId": "00c61b117ca639154fd1febe2b89a52f725f5315a4ddea4ab3a7ca714196d74849fe41fbc1",
|
|
200
|
+
|
|
201
|
+
"appEngineRelease": "1.9.65",
|
|
202
|
+
|
|
203
|
+
"traceId": "87d8451f8d11498fbba715077fbb2aac",
|
|
204
|
+
|
|
205
|
+
"first": true,
|
|
206
|
+
|
|
207
|
+
"traceSampled": true
|
|
208
|
+
|
|
209
|
+
},
|
|
210
|
+
|
|
211
|
+
"insertId": "5c21fa78000cc41525d59178",
|
|
212
|
+
|
|
213
|
+
"httpRequest": {
|
|
214
|
+
|
|
215
|
+
"status": 404
|
|
216
|
+
|
|
217
|
+
},
|
|
218
|
+
|
|
219
|
+
"resource": {
|
|
220
|
+
|
|
221
|
+
"type": "gae_app",
|
|
222
|
+
|
|
223
|
+
"labels": {
|
|
224
|
+
|
|
225
|
+
"module_id": "default",
|
|
226
|
+
|
|
227
|
+
"zone": "asia-northeast1-1",
|
|
228
|
+
|
|
229
|
+
"project_id": "fluid-emissary-216806",
|
|
230
|
+
|
|
231
|
+
"version_id": "20181213t142210"
|
|
232
|
+
|
|
233
|
+
}
|
|
234
|
+
|
|
235
|
+
},
|
|
236
|
+
|
|
237
|
+
"timestamp": "2018-12-25T09:38:00.828681Z",
|
|
238
|
+
|
|
239
|
+
"labels": {
|
|
240
|
+
|
|
241
|
+
"clone_id": "00c61b117ca639154fd1febe2b89a52f725f5315a4ddea4ab3a7ca714196d74849fe41fbc1"
|
|
242
|
+
|
|
243
|
+
},
|
|
244
|
+
|
|
245
|
+
"logName": "projects/fluid-emissary-216806/logs/appengine.googleapis.com%2Frequest_log",
|
|
246
|
+
|
|
247
|
+
"operation": {
|
|
248
|
+
|
|
249
|
+
"id": "5c21fa7800ff0ca50930d01c390001627e666c7569642d656d6973736172792d3231363830360001323031383132313374313432323130000100",
|
|
250
|
+
|
|
251
|
+
"producer": "appengine.googleapis.com/request_id",
|
|
252
|
+
|
|
253
|
+
"first": true,
|
|
254
|
+
|
|
255
|
+
"last": true
|
|
256
|
+
|
|
257
|
+
},
|
|
258
|
+
|
|
259
|
+
"trace": "projects/fluid-emissary-216806/traces/87d8451f8d11498fbba715077fbb2aac",
|
|
260
|
+
|
|
261
|
+
"receiveTimestamp": "2018-12-25T09:38:00.859965517Z",
|
|
262
|
+
|
|
263
|
+
"traceSampled": true
|
|
264
|
+
|
|
265
|
+
},
|
|
266
|
+
|
|
267
|
+
{
|
|
268
|
+
|
|
269
|
+
"protoPayload": {
|
|
270
|
+
|
|
271
|
+
"@type": "type.googleapis.com/google.appengine.logging.v1.RequestLog",
|
|
272
|
+
|
|
273
|
+
"appId": "b~fluid-emissary-216806",
|
|
274
|
+
|
|
275
|
+
"versionId": "20181213t142210",
|
|
276
|
+
|
|
277
|
+
"requestId": "5c21fa3c00ff01b09bdc7ce1370001627e666c7569642d656d6973736172792d3231363830360001323031383132313374313432323130000100",
|
|
278
|
+
|
|
279
|
+
"ip": "0.1.0.1",
|
|
280
|
+
|
|
281
|
+
"startTime": "2018-12-25T09:37:00.110747Z",
|
|
282
|
+
|
|
283
|
+
"endTime": "2018-12-25T09:37:06.738707Z",
|
|
284
|
+
|
|
285
|
+
"latency": "6.627960s",
|
|
286
|
+
|
|
287
|
+
"megaCycles": "6843",
|
|
288
|
+
|
|
289
|
+
"method": "GET",
|
|
290
|
+
|
|
291
|
+
"resource": "/home/dwhtest01/cron/bigquery_tbl_gousei.sql",
|
|
292
|
+
|
|
293
|
+
"httpVersion": "HTTP/1.1",
|
|
294
|
+
|
|
295
|
+
"status": 404,
|
|
296
|
+
|
|
297
|
+
"responseSize": "270",
|
|
298
|
+
|
|
299
|
+
"userAgent": "AppEngine-Google; (+http://code.google.com/appengine)",
|
|
300
|
+
|
|
301
|
+
"urlMapEntry": "unused",
|
|
302
|
+
|
|
303
|
+
"host": "fluid-emissary-216806.appspot.com",
|
|
304
|
+
|
|
305
|
+
"cost": 3.9227e-8,
|
|
306
|
+
|
|
307
|
+
"taskQueueName": "__cron",
|
|
308
|
+
|
|
309
|
+
"taskName": "628d389511e5aca63075275b587e7723",
|
|
310
|
+
|
|
311
|
+
"wasLoadingRequest": true,
|
|
312
|
+
|
|
313
|
+
"instanceIndex": -1,
|
|
314
|
+
|
|
315
|
+
"finished": true,
|
|
316
|
+
|
|
317
|
+
"instanceId": "00c61b117ca639154fd1febe2b89a52f725f5315a4ddea4ab3a7ca714196d74849fe41fbc1",
|
|
318
|
+
|
|
319
|
+
"line": [
|
|
320
|
+
|
|
321
|
+
{
|
|
322
|
+
|
|
323
|
+
"time": "2018-12-25T09:37:06.738545Z",
|
|
324
|
+
|
|
325
|
+
"severity": "INFO",
|
|
326
|
+
|
|
327
|
+
"logMessage": "This request caused a new process to be started for your application, and thus caused your application code to be loaded for the first time. This request may thus take longer and use more CPU than a typical request for your application."
|
|
328
|
+
|
|
329
|
+
}
|
|
330
|
+
|
|
331
|
+
],
|
|
332
|
+
|
|
333
|
+
"appEngineRelease": "1.9.65",
|
|
334
|
+
|
|
335
|
+
"traceId": "7f55ee9515b0864f014b7f477c927d56",
|
|
336
|
+
|
|
337
|
+
"first": true,
|
|
338
|
+
|
|
339
|
+
"traceSampled": true
|
|
340
|
+
|
|
341
|
+
},
|
|
342
|
+
|
|
343
|
+
"insertId": "5c21fa42000b46494943821e",
|
|
344
|
+
|
|
345
|
+
"httpRequest": {
|
|
346
|
+
|
|
347
|
+
"status": 404
|
|
348
|
+
|
|
349
|
+
},
|
|
350
|
+
|
|
351
|
+
"resource": {
|
|
352
|
+
|
|
353
|
+
"type": "gae_app",
|
|
354
|
+
|
|
355
|
+
"labels": {
|
|
356
|
+
|
|
357
|
+
"version_id": "20181213t142210",
|
|
358
|
+
|
|
359
|
+
"module_id": "default",
|
|
360
|
+
|
|
361
|
+
"zone": "asia-northeast1-1",
|
|
362
|
+
|
|
363
|
+
"project_id": "fluid-emissary-216806"
|
|
364
|
+
|
|
365
|
+
}
|
|
366
|
+
|
|
367
|
+
},
|
|
368
|
+
|
|
369
|
+
"timestamp": "2018-12-25T09:37:00.110747Z",
|
|
370
|
+
|
|
371
|
+
"severity": "INFO",
|
|
372
|
+
|
|
373
|
+
"labels": {
|
|
374
|
+
|
|
375
|
+
"clone_id": "00c61b117ca639154fd1febe2b89a52f725f5315a4ddea4ab3a7ca714196d74849fe41fbc1"
|
|
376
|
+
|
|
377
|
+
},
|
|
378
|
+
|
|
379
|
+
"logName": "projects/fluid-emissary-216806/logs/appengine.googleapis.com%2Frequest_log",
|
|
380
|
+
|
|
381
|
+
"operation": {
|
|
382
|
+
|
|
383
|
+
"id": "5c21fa3c00ff01b09bdc7ce1370001627e666c7569642d656d6973736172792d3231363830360001323031383132313374313432323130000100",
|
|
384
|
+
|
|
385
|
+
"producer": "appengine.googleapis.com/request_id",
|
|
386
|
+
|
|
387
|
+
"first": true,
|
|
388
|
+
|
|
389
|
+
"last": true
|
|
390
|
+
|
|
391
|
+
},
|
|
392
|
+
|
|
393
|
+
"trace": "projects/fluid-emissary-216806/traces/7f55ee9515b0864f014b7f477c927d56",
|
|
394
|
+
|
|
395
|
+
"receiveTimestamp": "2018-12-25T09:37:06.765183723Z",
|
|
396
|
+
|
|
397
|
+
"traceSampled": true
|
|
398
|
+
|
|
399
|
+
}
|
|
400
|
+
|
|
401
|
+
]
|
|
402
|
+
|
|
403
|
+
```
|
5
一部変更
test
CHANGED
|
@@ -1 +1 @@
|
|
|
1
|
-
BigQuery上で実行するクエリをGAEのcronジョブで定期実行(1分間隔で実行)
|
|
1
|
+
〖再掲〗BigQuery上で実行するクエリをGAEのcronジョブで定期実行(1分間隔で実行)
|
test
CHANGED
|
File without changes
|
4
状況更新
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -40,8 +40,20 @@
|
|
|
40
40
|
|
|
41
41
|
スケジューリングする際に、1分毎に実行する条件を追加
|
|
42
42
|
|
|
43
|
+
```
|
|
43
44
|
|
|
44
45
|
|
|
46
|
+
|
|
45
|
-
|
|
47
|
+
※2018/12/25
|
|
46
48
|
|
|
47
49
|
```
|
|
50
|
+
|
|
51
|
+
Cloud Shellはログアウトして1時間経過するとcrontabの中身が消える
|
|
52
|
+
|
|
53
|
+
Schedule queryでは、実行間隔の最短許容時間は 3 時間となる
|
|
54
|
+
|
|
55
|
+
|
|
56
|
+
|
|
57
|
+
⇒bigqueryでSQLを1分間隔で定期実行する方法を知りたいです
|
|
58
|
+
|
|
59
|
+
```
|
3
質問更新
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -29,3 +29,19 @@
|
|
|
29
29
|
|
|
30
30
|
|
|
31
31
|
１と２について調査中となります。
|
|
32
|
+
|
|
33
|
+
|
|
34
|
+
|
|
35
|
+
※2018/12/21
|
|
36
|
+
|
|
37
|
+
```ここに言語を入力
|
|
38
|
+
|
|
39
|
+
スケジュールクエリで、SQLをスケジューリング
|
|
40
|
+
|
|
41
|
+
スケジューリングする際に、1分毎に実行する条件を追加
|
|
42
|
+
|
|
43
|
+
|
|
44
|
+
|
|
45
|
+
⇒実装可能か調査中
|
|
46
|
+
|
|
47
|
+
```
|
2
質問内容更新
test
CHANGED
|
@@ -1 +1 @@
|
|
|
1
|
-
BigQuery上で実行するクエリをGAEのcronジョブで定期実行
|
|
1
|
+
BigQuery上で実行するクエリをGAEのcronジョブで定期実行(1分間隔で実行)
|
test
CHANGED
|
@@ -18,7 +18,7 @@
|
|
|
18
18
|
|
|
19
19
|
|
|
20
20
|
|
|
21
|
-
CronJobを使って、➂の処理を定期実行させたい。
|
|
21
|
+
CronJobを使って、➂の処理を定期実行（1分間隔で実行）させたい。
|
|
22
22
|
|
|
23
23
|
|
|
24
24
|
|
1
質問更新
test
CHANGED
|
@@ -1 +1 @@
|
|
|
1
|
-
|
|
1
|
+
BigQuery上で実行するクエリをGAEのcronジョブで定期実行
|
test
CHANGED
|
@@ -1,6 +1,6 @@
|
|
|
1
1
|
■実現したいこと
|
|
2
2
|
|
|
3
|
-
BigQueryに３つのテーブルを作成して、
|
|
3
|
+
BigQueryに３つのテーブルを作成して、cronジョブでテーブル更新処理を定期実行させる
|
|
4
4
|
|
|
5
5
|
|
|
6
6
|
|
|
@@ -18,8 +18,14 @@
|
|
|
18
18
|
|
|
19
19
|
|
|
20
20
|
|
|
21
|
-
|
|
21
|
+
CronJobを使って、➂の処理を定期実行させたい。
|
|
22
22
|
|
|
23
23
|
|
|
24
24
|
|
|
25
|
+
１．cronjobの定義方法
|
|
26
|
+
|
|
27
|
+
２．BigQueryのクエリ定義方法
|
|
28
|
+
|
|
29
|
+
|
|
30
|
+
|
|
25
|
-
|
|
31
|
+
１と２について調査中となります。
|