■実現したいこと
BigQueryに3つのテーブルを作成して、cronジョブでテーブル更新処理を定期実行させる
■実装案
➀OracleDBのデータをembulkでBigQueryへバルクインサートする。対象のテーブルは2つ(A,B)
➁BigQueryに作成された2つのテーブル(A,B)、別途BigQueryにテーブルを1つ(C)作成する
➂2つのテーブル(A,B)から、sqlを使って特定条件のもと結合処理を行い、必要なレコードだけをCテーブルへインサートする
CronJobを使って、➂の処理を定期実行(1分間隔で実行)させたい。
1.cronjobの定義方法
2.BigQueryのクエリ定義方法
※2018/12/21
スケジュールクエリで、SQLをスケジューリング スケジューリングする際に、1分毎に実行する条件を追加
※2018/12/25
Cloud Shellはログアウトして1時間経過するとcrontabの中身が消える Schedule queryでは、実行間隔の最短許容時間は 3 時間となる ⇒bigqueryでSQLを1分間隔で定期実行する方法を知りたいです
※2018/12/25
※※実行させたいクエリ※※ dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$ cat bigquery_tbl_gousei.sql insert into `fluid-emissary-216806.embulk_test.INPUT_TEST_003` (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) select * from `fluid-emissary-216806.embulk_test.INPUT_TEST_001` where Num In (select Num from `fluid-emissary-216806.embulk_test.INPUT_TEST_002` A where not exists(select 1 from `fluid-emissary-216806.embulk_test.INPUT_TEST_003` B where A.NUM=B.NUM)); commit; ※※クーロン定義※※ dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$ cat cron.yml cron: - description: "test job" url: /home/dwhtest01/cron/bigquery_tbl_gousei.sql schedule: every 1 mins dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$ dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$ ※※デプロイ※※ dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$ gcloud app deploy cron.yml Configurations to update: descriptor: [/home/dwhtest01/cron/cron.yml] type: [cron jobs] target project: [fluid-emissary-216806] Do you want to continue (Y/n)? y Updating config [cron]...done. Cron jobs have been updated. Visit the Cloud Platform Console Task Queues page to view your queues and cron jobs. https://console.cloud.google.com/appengine/taskqueues/cron?project=fluid-emissary-216806 dwhtest01@cloudshell:~/cron (fluid-emissary-216806)$ ※※エラーログ※※ [ { "protoPayload": { "@type": "type.googleapis.com/google.appengine.logging.v1.RequestLog", "appId": "b~fluid-emissary-216806", "versionId": "20181213t142210", "requestId": "5c21fa7800ff0ca50930d01c390001627e666c7569642d656d6973736172792d3231363830360001323031383132313374313432323130000100", "ip": "0.1.0.1", "startTime": "2018-12-25T09:38:00.828681Z", "endTime": "2018-12-25T09:38:00.836503Z", "latency": "0.007822s", "megaCycles": "18", "method": "GET", "resource": "/home/dwhtest01/cron/bigquery_tbl_gousei.sql", "httpVersion": "HTTP/1.1", "status": 404, "responseSize": "270", "userAgent": "AppEngine-Google; (+http://code.google.com/appengine)", "urlMapEntry": "unused", "host": "fluid-emissary-216806.appspot.com", "cost": 3.9227e-8, "taskQueueName": "__cron", "taskName": "628d389511e5aca63075275b587e7723", "instanceIndex": -1, "finished": true, "instanceId": "00c61b117ca639154fd1febe2b89a52f725f5315a4ddea4ab3a7ca714196d74849fe41fbc1", "appEngineRelease": "1.9.65", "traceId": "87d8451f8d11498fbba715077fbb2aac", "first": true, "traceSampled": true }, "insertId": "5c21fa78000cc41525d59178", "httpRequest": { "status": 404 }, "resource": { "type": "gae_app", "labels": { "module_id": "default", "zone": "asia-northeast1-1", "project_id": "fluid-emissary-216806", "version_id": "20181213t142210" } }, "timestamp": "2018-12-25T09:38:00.828681Z", "labels": { "clone_id": "00c61b117ca639154fd1febe2b89a52f725f5315a4ddea4ab3a7ca714196d74849fe41fbc1" }, "logName": "projects/fluid-emissary-216806/logs/appengine.googleapis.com%2Frequest_log", "operation": { "id": "5c21fa7800ff0ca50930d01c390001627e666c7569642d656d6973736172792d3231363830360001323031383132313374313432323130000100", "producer": "appengine.googleapis.com/request_id", "first": true, "last": true }, "trace": "projects/fluid-emissary-216806/traces/87d8451f8d11498fbba715077fbb2aac", "receiveTimestamp": "2018-12-25T09:38:00.859965517Z", "traceSampled": true }, { "protoPayload": { "@type": "type.googleapis.com/google.appengine.logging.v1.RequestLog", "appId": "b~fluid-emissary-216806", "versionId": "20181213t142210", "requestId": "5c21fa3c00ff01b09bdc7ce1370001627e666c7569642d656d6973736172792d3231363830360001323031383132313374313432323130000100", "ip": "0.1.0.1", "startTime": "2018-12-25T09:37:00.110747Z", "endTime": "2018-12-25T09:37:06.738707Z", "latency": "6.627960s", "megaCycles": "6843", "method": "GET", "resource": "/home/dwhtest01/cron/bigquery_tbl_gousei.sql", "httpVersion": "HTTP/1.1", "status": 404, "responseSize": "270", "userAgent": "AppEngine-Google; (+http://code.google.com/appengine)", "urlMapEntry": "unused", "host": "fluid-emissary-216806.appspot.com", "cost": 3.9227e-8, "taskQueueName": "__cron", "taskName": "628d389511e5aca63075275b587e7723", "wasLoadingRequest": true, "instanceIndex": -1, "finished": true, "instanceId": "00c61b117ca639154fd1febe2b89a52f725f5315a4ddea4ab3a7ca714196d74849fe41fbc1", "line": [ { "time": "2018-12-25T09:37:06.738545Z", "severity": "INFO", "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." } ], "appEngineRelease": "1.9.65", "traceId": "7f55ee9515b0864f014b7f477c927d56", "first": true, "traceSampled": true }, "insertId": "5c21fa42000b46494943821e", "httpRequest": { "status": 404 }, "resource": { "type": "gae_app", "labels": { "version_id": "20181213t142210", "module_id": "default", "zone": "asia-northeast1-1", "project_id": "fluid-emissary-216806" } }, "timestamp": "2018-12-25T09:37:00.110747Z", "severity": "INFO", "labels": { "clone_id": "00c61b117ca639154fd1febe2b89a52f725f5315a4ddea4ab3a7ca714196d74849fe41fbc1" }, "logName": "projects/fluid-emissary-216806/logs/appengine.googleapis.com%2Frequest_log", "operation": { "id": "5c21fa3c00ff01b09bdc7ce1370001627e666c7569642d656d6973736172792d3231363830360001323031383132313374313432323130000100", "producer": "appengine.googleapis.com/request_id", "first": true, "last": true }, "trace": "projects/fluid-emissary-216806/traces/7f55ee9515b0864f014b7f477c927d56", "receiveTimestamp": "2018-12-25T09:37:06.765183723Z", "traceSampled": true } ]
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/12/21 00:56
2018/12/21 01:15 編集
2018/12/21 01:45
2018/12/21 01:57