〖再掲〗BigQuery上で実行するクエリをGAEのcronジョブで定期実行(1分間隔で実行)

■実現したいこと
BigQueryに３つのテーブルを作成して、cronジョブでテーブル更新処理を定期実行させる

■実装案
➀OracleDBのデータをembulkでBigQueryへバルクインサートする。対象のテーブルは２つ(A,B)
➁BigQueryに作成された2つのテーブル（A,B）、別途BigQueryにテーブルを１つ(C)作成する
➂2つのテーブル(A,B)から、sqlを使って特定条件のもと結合処理を行い、必要なレコードだけをCテーブルへインサートする
CronJobを使って、➂の処理を定期実行（1分間隔で実行）させたい。

１．cronjobの定義方法
２．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
  }
]