{"id":17834,"date":"2019-03-01T17:37:45","date_gmt":"2019-03-01T08:37:45","guid":{"rendered":"https:\/\/labs.gree.jp\/blog\/?p=17834"},"modified":"2019-05-13T20:50:51","modified_gmt":"2019-05-13T11:50:51","slug":"post17834","status":"publish","type":"post","link":"https:\/\/labs.gree.jp\/blog\/2019\/03\/17834\/","title":{"rendered":"Fargate\u74b0\u5883\u3067embulk\u3092\u4f7f\u3063\u3066MySQL\u304b\u3089BigQuery\u3078\u306e\u30de\u30b9\u30bf\u30c7\u30fc\u30bf\u8ee2\u9001"},"content":{"rendered":"<p>\u4f4f\u307e\u3044\u66ae\u3089\u3057\u30e1\u30c7\u30a3\u30a2<a href=\"https:\/\/limia.jp\/\">LIMIA<\/a>\u3067\u958b\u767a\u3092\u62c5\u5f53\u3057\u3066\u3044\u308b<a href=\"https:\/\/twitter.com\/mahiguch1\">\u6a0b\u53e3<\/a>\u3067\u3059\u3002<br \/>\nLIMIA\u3067\u306fBigQuery\u3092\u4f7f\u3063\u3066\u30c7\u30fc\u30bf\u5206\u6790\u3092\u884c\u306a\u3063\u3066\u3044\u307e\u3059\u3002<br \/>\n\u30ed\u30b0\u30c7\u30fc\u30bf\u306b\u3064\u3044\u3066\u306fFirebase Analytics\u306b\u9001\u4fe1\u3057\u305f\u30c7\u30fc\u30bf\u3092BigQuery\u3078\u66f8\u304d\u51fa\u3057\u3066\u3044\u307e\u3059\u3002<br \/>\n\u30de\u30b9\u30bf\u30c7\u30fc\u30bf\u306b\u3064\u3044\u3066\u306fMySQL\u306b\u683c\u7d0d\u3055\u308c\u3066\u304a\u308a\u3001\u305d\u308c\u3092BigQuery\u3078\u9001\u4fe1\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3057\u305f\u3002<br \/>\nembulk\u3092fargate\u74b0\u5883\u3067\u52d5\u304b\u3059\u3053\u3068\u3067\u5b9f\u73fe\u3057\u305f\u305f\u3081\u3001\u305d\u308c\u306b\u3064\u3044\u3066\u5171\u6709\u3057\u307e\u3059\u3002<\/p>\n<h2>embulk\u306b\u3064\u3044\u3066<\/h2>\n<p>embulk\u306f\u30c7\u30fc\u30bf\u30ed\u30fc\u30c0\u3067\u3059\u3002<br \/>\nfluentd\u306e\u30d0\u30c3\u30c1\u7248\u3068\u8a00\u308f\u308c\u3066\u304a\u308a\u3001\u30aa\u30fc\u30d7\u30f3\u30bd\u30fc\u30b9\u3067\u958b\u767a\u3055\u308c\u3066\u3044\u307e\u3059\u3002<br \/>\nfluentd\u3068\u540c\u3058\u3088\u3046\u306b\u30d7\u30e9\u30b0\u30a4\u30f3\u3092\u5c0e\u5165\u3059\u308b\u3053\u3068\u3067\u3001\u69d8\u3005\u306a\u30c7\u30fc\u30bf\u30bd\u30fc\u30b9\u306b\u5bfe\u5fdc\u3057\u3066\u3044\u307e\u3059\u3002<br \/>\nLIMIA\u3067\u306f\u5165\u529b\u3092MySQL\u3001\u51fa\u529b\u3092BigQuery\u3068\u3057\u3066\u3044\u307e\u3059\u3002<br \/>\n\u305d\u306e\u305f\u3081\u3001\u30d7\u30e9\u30b0\u30a4\u30f3\u3068\u3057\u3066 embulk-input-mysql \u3068 embulk-output-bigquery \u3092\u5229\u7528\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>\u4eca\u56de\u306e\u30c7\u30fc\u30bf\u3092\u8ee2\u9001\u3059\u308b\u306b\u306f\u3001\u30c6\u30fc\u30d6\u30eb\u6bce\u306b2\u3064\u306e\u30d5\u30a1\u30a4\u30eb\u3092\u7528\u610f\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<br \/>\n1\u3064\u306f\u8ee2\u9001\u5148\u306eBigQuery\u30c6\u30fc\u30d6\u30eb\u306eschema\u3092\u5b9a\u7fa9\u3059\u308b\u30d5\u30a1\u30a4\u30eb\u3067\u3001\u6b21\u306e\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<p>files\/etc\/embulk\/schema\/idea.json<\/p>\n<pre class=\"lang:js decode:true\" title=\"files\/etc\/embulk\/schema\/idea.json\">[\n  {\"name\":\"id\", \"type\":\"integer\", \"mode\": \"required\"},\n  {\"name\":\"status\", \"type\":\"integer\", \"mode\": \"required\"},\n  {\"name\":\"user_id\", \"type\":\"integer\", \"mode\": \"required\"},\n  {\"name\":\"title\", \"type\":\"string\", \"mode\": \"nullable\"},\n  {\"name\":\"created_at\", \"type\":\"timestamp\", \"mode\": \"required\"},\n  {\"name\":\"updated_at\", \"type\":\"timestamp\", \"mode\": \"required\"}\n]<\/pre>\n<p>\u3082\u30461\u3064\u306f\u8ee2\u9001\u8a2d\u5b9a\u30d5\u30a1\u30a4\u30eb\u3067\u3059\u3002<br \/>\n\u30d5\u30a1\u30a4\u30eb\u540d\u306e\u6700\u5f8c\u306b.liquid\u3068\u4ed8\u3051\u308b\u3068\u3001\u4e8c\u91cd\u62ec\u5f27 {{ }} \u3092\u5c55\u958b\u3057\u3066\u304f\u308c\u307e\u3059\u3002<br \/>\nenv. \u3067\u74b0\u5883\u5909\u6570\u3092\u53d6\u5f97\u3067\u304d\u308b\u305f\u3081\u3001\u30d1\u30b9\u30ef\u30fc\u30c9\u306a\u3069\u3092\u3053\u3053\u306b\u683c\u7d0d\u3057\u3066\u3044\u307e\u3059\u3002<br \/>\n\u4ee5\u4e0b\u306e\u8a2d\u5b9a\u3067\u306f\u3001MySQL\u306e\u5bfe\u8c61\u30c6\u30fc\u30d6\u30eb\u5168\u884c\u3092\u30c0\u30f3\u30d7\u3057\u3001\u305d\u308c\u3092BigQuery\u306b\u8ee2\u9001\u3057\u3066\u7f6e\u304d\u63db\u3048\u307e\u3059\u3002<\/p>\n<p>files\/etc\/embulk\/tables\/idea.yml.liquid<\/p>\n<pre class=\"lang:default decode:true \" title=\"files\/etc\/embulk\/tables\/idea.yml.liquid\">in:\n    type: mysql\n    user: {{ env.LIMIA_DB_USER }}\n    password: {{ env.LIMIA_DB_PASS }}\n    host: {{ env.LIMIA_DB_HOST_MAIN }}\n    database: fily_communication\n    table: idea\n    select: \"*\"\n    options: {useLegacyDatetimeCode: false, serverTimezone: Asia\/Tokyo}\n    default_timezone: \"Asia\/Tokyo\"\n    parser:\n        type: json\nout:\n    type: bigquery\n    mode: replace\n    auth_method: json_key\n    json_keyfile: \/etc\/bigquery_service_account.json\n    path_prefix: \/tmp\/\n    file_ext: .jsonl.gz\n    source_format: NEWLINE_DELIMITED_JSON\n    project: {{ env.GCP_PROJECT_ID }}\n    dataset: fily_communication\n    auto_create_table: true\n    table: idea\n    schema_file: \/etc\/embulk\/schema\/idea.json\n    formatter: {type: jsonl}\n    encoders:\n        - {type: gzip}<\/pre>\n<h2>\u30b3\u30f3\u30c6\u30ca\u8a2d\u5b9a<\/h2>\n<p>embulk\u3092\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u3057\u3066\u3001\u5b9f\u884c\u6642\u306bshell script\u3092\u53e9\u304f\u3088\u3046\u306aDocker\u30d5\u30a1\u30a4\u30eb\u3092\u4f5c\u308a\u307e\u3059\u3002<\/p>\n<p>Dockerfile<\/p>\n<pre class=\"lang:default decode:true \" title=\"Dockerfile\">FROM openjdk:8-jre-alpine\n\nRUN apk add --no-cache libc6-compat python py2-pip coreutils tzdata &amp;&amp; \\\n  cp \/usr\/share\/zoneinfo\/Asia\/Tokyo \/etc\/localtime &amp;&amp; \\\n  echo \"Asia\/Tokyo\" &gt; \/etc\/timezone\n\nRUN pip install awscli\n\nRUN wget -q https:\/\/dl.embulk.org\/embulk-latest.jar -O \/usr\/local\/bin\/embulk &amp;&amp; \\\n  chmod +x \/usr\/local\/bin\/embulk\n\nRUN \/usr\/local\/bin\/embulk gem install embulk-input-mysql &amp;&amp; \\\n  \/usr\/local\/bin\/embulk gem install embulk-output-bigquery\n\nCOPY files\/etc\/embulk \/etc\/embulk\nCOPY files\/entrypoint.sh \/entrypoint.sh\n\nENTRYPOINT [\"\/entrypoint.sh\"]<\/pre>\n<p>embulk\u3067\u8ee2\u9001\u3092\u5b9f\u884c\u3059\u308b\u305f\u3081\u306b\u306f\u3001\u30b3\u30de\u30f3\u30c9\u30e9\u30a4\u30f3\u3067\u6b21\u306e\u3088\u3046\u306b\u6253\u3061\u307e\u3059\u3002<\/p>\n<pre class=\"lang:default decode:true\">embulk run \u8ee2\u9001\u8a2d\u5b9a\u30d5\u30a1\u30a4\u30eb.yml.liquid<\/pre>\n<p>LIMIA\u3067\u306f\u8907\u6570\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u8ee2\u9001\u3057\u3066\u3044\u308b\u305f\u3081\u3001\u7279\u5b9a\u306e\u30c7\u30a3\u30ec\u30af\u30c8\u30ea\u306b\u8ee2\u9001\u8a2d\u5b9a\u30d5\u30a1\u30a4\u30eb\u3092\u683c\u7d0d\u3057\u3066\u304a\u304d\u3001process_tables()\u306e\u51e6\u7406\u3067\u305d\u308c\u3092\u4e00\u62ec\u5b9f\u884c\u3057\u3066\u3044\u307e\u3059\u3002<br \/>\n\u307e\u305f\u3001\u7279\u5b9a\u306e\u30c6\u30fc\u30d6\u30eb\u306e\u307f\u6307\u5b9a\u6642\u523b\u306b\u8ee2\u9001\u3059\u308b\u8981\u671b\u304c\u3042\u308b\u305f\u3081\u3001process_specified()\u3067\u500b\u5225\u8ee2\u9001\u53ef\u80fd\u3068\u3057\u3066\u3044\u307e\u3059\u3002<br \/>\nBigQuery\u306b\u8ee2\u9001\u3059\u308b\u305f\u3081\u306b\u306f\u3001service account\u3068\u547c\u3070\u308c\u308b\u6a29\u9650\u3092\u4e0e\u3048\u3089\u308c\u305f\u9375\u30d5\u30a1\u30a4\u30eb\u304c\u5fc5\u8981\u3067\u3059\u3002<br \/>\n\u305d\u308c\u3092\u6697\u53f7\u5316\u3057\u3066 blob\/${LIMIA_ENV}-bigquery-service-account.blob \u306b\u7f6e\u3044\u3066\u304a\u304d\u3001\u6697\u53f7\u5316\u9375\u3092KMS\u306b\u767b\u9332\u3057\u3066\u304a\u304d\u307e\u3059\u3002<br \/>\ndecypt()\u306e\u51e6\u7406\u3067service account\u3092\u5fa9\u53f7\u5316\u3057\u3066\u3001\u30b3\u30f3\u30c6\u30ca\u5185\u306e\u30d5\u30a1\u30a4\u30eb\u306b\u4fdd\u5b58\u3057\u3066\u3044\u307e\u3059\u3002<br \/>\nGCP_PROJECT_ID \u3068 LIMIA_ENV \u306f\u3001\u74b0\u5883\u5909\u6570\u304b\u3089\u53d6\u5f97\u3057\u3066\u3044\u307e\u3059\u3002<br \/>\n\u74b0\u5883\u5909\u6570\u306f\u3001ECS Task Definitions\u3067\u8a2d\u5b9a\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>files\/entrypoint.sh<\/p>\n<pre class=\"lang:default decode:true \" title=\"entrypoint.sh\">#!\/bin\/sh\n\ndecrypt() {\n    aws kms decrypt --ciphertext-blob fileb:\/\/\/etc\/embulk\/blob\/${LIMIA_ENV}-bigquery-service-account.blob --output text --query Plaintext | base64 -d &gt; \/etc\/bigquery_service_account.json\n}\n\nprocess_tables() {\n    ls \/etc\/embulk\/tables\/*.yml.liquid | xargs -n1 java -jar \/usr\/local\/bin\/embulk run 2&gt;&amp;1\n}\n\nprocess_specified() {\n    for var in $@; do\n      ls \/etc\/embulk\/tables\/${var}.yml.liquid | xargs -n1 java -jar \/usr\/local\/bin\/embulk run 2&gt;&amp;1\n    done\n}\n\ninit() {\n    if [ -z \"${GCP_PROJECT_ID}\" ]; then\n      echo no GCP_PROJECT_ID\n      exit\n    fi\n    if [ -z \"${LIMIA_ENV}\" ]; then\n      echo no LIMIA_ENV\n      exit\n    fi\n\n    decrypt\n\n    if [ $# -eq 0 ]; then\n        process_tables\n    else\n        process_specified \"$@\"\n    fi\n}\n\ninit \"$@\"<\/pre>\n<h2>\u307e\u3068\u3081<\/h2>\n<p>embulk\u3092\u4f7f\u3063\u3066MySQL\u306e\u30de\u30b9\u30bf\u30c7\u30fc\u30bf\u3092BigQuery\u306b\u8ee2\u9001\u3059\u308b\u65b9\u6cd5\u3092\u8aac\u660e\u3057\u307e\u3057\u305f\u3002<br \/>\n\u3053\u308c\u306b\u3088\u308a\u3001\u30de\u30b9\u30bf\u30c7\u30fc\u30bf\u3092\u7d61\u3081\u305f\u5206\u6790\u304c\u53ef\u80fd\u3068\u306a\u308a\u307e\u3057\u305f\u3002<br \/>\nECS\/Fargate\u306b\u3064\u3044\u3066\u306f\u8efd\u304f\u89e6\u308c\u305f\u7a0b\u5ea6\u3067\u3059\u306e\u3067\u3001\u53ef\u80fd\u306a\u3089\u6b21\u56de\u305d\u306e\u8fba\u308a\u306b\u3064\u3044\u3066\u3082\u5171\u6709\u3057\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<p>\u307e\u305f\u3001Fargate\u74b0\u5883\u3067<a href=\"https:\/\/labs.gree.jp\/blog\/2019\/03\/17850\/\">S3\u306b\u3042\u308bALB\u3084CloudFront\u306e\u30ed\u30b0\u3092BigQuery\u306b\u8ee2\u9001\u3059\u308b\u65b9\u6cd5<\/a>\u3082\u66f8\u304d\u307e\u3057\u305f\u3002\u3082\u3057\u3088\u3051\u308c\u3070\u3001\u5408\u308f\u305b\u3066\u3053\u3061\u3089\u3082\u3054\u89a7\u304f\u3060\u3055\u3044\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4f4f\u307e\u3044\u66ae\u3089\u3057\u30e1\u30c7\u30a3\u30a2LIMIA\u3067\u958b\u767a\u3092\u62c5\u5f53\u3057\u3066\u3044\u308b\u6a0b\u53e3\u3067\u3059\u3002 LIMIA\u3067\u306fBigQuery\u3092\u4f7f\u3063\u3066\u30c7\u30fc\u30bf\u5206\u6790\u3092\u884c\u306a\u3063\u3066\u3044\u307e\u3059\u3002 \u30ed\u30b0\u30c7\u30fc\u30bf\u306b\u3064\u3044\u3066\u306fFirebase Analytics\u306b\u9001\u4fe1\u3057\u305f\u30c7\u30fc\u30bf\u3092BigQuery [&hellip;]<\/p>\n","protected":false},"author":195,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9],"tags":[114,157,140,160,152,141,17],"class_list":["post-17834","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-info","tag-aws","tag-bigquery","tag-ecs","tag-embulk","tag-fargate","tag-gcp","tag-mysql"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/17834","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/users\/195"}],"replies":[{"embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/comments?post=17834"}],"version-history":[{"count":3,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/17834\/revisions"}],"predecessor-version":[{"id":17915,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/17834\/revisions\/17915"}],"wp:attachment":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/media?parent=17834"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/categories?post=17834"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/tags?post=17834"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}