{"id":760,"date":"2019-02-07T08:31:19","date_gmt":"2019-02-06T23:31:19","guid":{"rendered":"https:\/\/cloud-textbook.com\/?p=760"},"modified":"2019-02-08T14:03:39","modified_gmt":"2019-02-08T05:03:39","slug":"2019-02-04-gcp%e3%81%aebigquery%e3%81%ab%e3%80%81information_schema%e3%81%a8alter-table%e3%81%8c%e8%bf%bd%e5%8a%a0%e3%83%99%e3%83%bc%e3%82%bf","status":"publish","type":"post","link":"https:\/\/cloud-textbook.com\/760\/","title":{"rendered":"GCP\u306eBigQuery\u306b\u3001INFORMATION_SCHEMA\u3068ALTER TABLE\u304c\u8ffd\u52a0(\u30d9\u30fc\u30bf)"},"content":{"rendered":"\n<p>2019\/02\/04 \u306b GCP \u306e BigQuery \u306b\u3001\u30e1\u30bf\u60c5\u5831 INFORMATION_SCHEMA\u3068\u3001ALTER TABLE\u304c\u8ffd\u52a0\u3055\u308c\u307e\u3057\u305f\u3002<\/p>\n\n\n\n<div id=\"toc_container\" class=\"no_bullets\"><p class=\"toc_title\">\u76ee\u6b21<\/p><ul class=\"toc_list\"><li><a href=\"#INFORMATION_SCHEMA\"><span class=\"toc_number toc_depth_1\">1<\/span> INFORMATION_SCHEMA<\/a><ul><li><a href=\"#INFORMATION_SCHEMA-2\"><span class=\"toc_number toc_depth_2\">1.1<\/span> INFORMATION_SCHEMA \u306e\u6599\u91d1<\/a><\/li><li><a href=\"#i\"><span class=\"toc_number toc_depth_2\">1.2<\/span> \u30c6\u30fc\u30d6\u30eb\u306e\u884c\u6570\u3068\u30b5\u30a4\u30ba<\/a><\/li><\/ul><\/li><li><a href=\"#ALTER_TABLE\"><span class=\"toc_number toc_depth_1\">2<\/span> ALTER TABLE<\/a><\/li><\/ul><\/div>\n<h2><span id=\"INFORMATION_SCHEMA\">INFORMATION_SCHEMA<\/span><\/h2>\n\n\n\n<p>\u30c7\u30fc\u30bf\u30bb\u30c3\u30c8\u4e00\u89a7\u3092\u53d6\u5f97\u3059\u308b\u306b\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306b\u3057\u307e\u3059\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n * EXCEPT(schema_owner)\nFROM\n INFORMATION_SCHEMA.SCHEMATA<\/code><\/pre>\n\n\n\n<p>\u7d50\u679c\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>  +----------------+---------------+---------------------+---------------------+-----------------+\n  |  catalog_name  |  schema_name  |    creation_time    | last_modified_time  |    location     |\n  +----------------+---------------+---------------------+---------------------+-----------------+\n  | myproject      | mydataset1    | 2018-11-07 19:50:24 | 2018-11-07 19:50:24 | US              |\n  | myproject      | mydataset2    | 2018-07-16 04:24:22 | 2018-07-16 04:24:22 | US              |\n  | myproject      | mydataset3    | 2018-02-07 21:08:45 | 2018-05-01 23:32:53 | asia-northeast1 |\n  +----------------+---------------+---------------------+---------------------+-----------------+\n<\/code><\/pre>\n\n\n\n<p>\u30c6\u30fc\u30d6\u30eb\u4e00\u89a7\u3092\u53d6\u5f97\u3059\u308b\u306b\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306b\u3057\u307e\u3059\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n * EXCEPT(is_typed)\nFROM\n mydataset.INFORMATION_SCHEMA.TABLES<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>  +----------------+---------------+----------------+------------+--------------------+---------------------+\n  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |\n  +----------------+---------------+----------------+------------+--------------------+---------------------+\n  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 |\n  | myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 |\n  +----------------+---------------+----------------+------------+--------------------+---------------------+<\/code><\/pre>\n\n\n\n<p>\u30ab\u30e9\u30e0\u60c5\u5831\u53d6\u5f97\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306b\u3057\u307e\u3059\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)\nFROM\n `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS\nWHERE\n table_name=\"population_by_zip_2010\"<\/code><\/pre>\n\n\n\n<p>\u7d50\u679c\u306f\u4e0b\u8a18\u3067\u3059\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+\n|       table_name       | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position |\n+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+\n| population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL |\n| population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |\n| population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |\n| population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |\n| population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |\n| population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |\n+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+<\/code><\/pre>\n\n\n\n<p>\u3055\u3089\u306a\u308b\u8a73\u7d30\u306f\u4e0b\u8a18\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n\n\n\n<p><a href=\"https:\/\/cloud.google.com\/bigquery\/docs\/information-schema-intro\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\" (\u65b0\u3057\u3044\u30bf\u30d6\u3067\u958b\u304f)\">https:\/\/cloud.google.com\/bigquery\/docs\/information-schema-intro<\/a><\/p>\n\n\n\n<h3><span id=\"INFORMATION_SCHEMA-2\">INFORMATION_SCHEMA \u306e\u6599\u91d1<\/span><\/h3>\n\n\n\n<p>INFORMATION_SCHEMA\u304b\u3089\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3059\u308b\u3068\u3001\u6700\u4f4e\u3067\u308210MB\u5206\u306e\u30af\u30a8\u30ea\u30b3\u30b9\u30c8\u304c\u304b\u304b\u308a\u307e\u3059\u3002\u306a\u305c\u306a\u3089\u30ad\u30e3\u30c3\u30b7\u30e5\u3055\u308c\u306a\u3044\u304b\u3089\u3060\u305d\u3046\u3067\u3059\u304c\u3001\u30e1\u30bf\u60c5\u5831\u3067\u304a\u91d1\u53d6\u308b\u3093\u304b\u3044! \u3063\u3066\u611f\u3058\u3067\u3059\u3002\u307e\u300110MB\u5206\u30670.00022\u5186\u3067\u3059\u3051\u3069\u306d\u3002<\/p>\n\n\n\n<h3><span id=\"i\">\u30c6\u30fc\u30d6\u30eb\u306e\u884c\u6570\u3068\u30b5\u30a4\u30ba<\/span><\/h3>\n\n\n\n<p>INFORMATION_SCHEMA \u306b\u3001\u30c6\u30fc\u30d6\u30eb\u306e\u884c\u6570\u3084\u30b5\u30a4\u30ba\u60c5\u5831\u306f\u542b\u307e\u308c\u3066\u3044\u307e\u305b\u3093\u3002\u3053\u308c\u307e\u3067\u901a\u308a\u30e1\u30bf\u30c6\u30fc\u30d6\u30eb [\u30c7\u30fc\u30bf\u30bb\u30c3\u30c8\u540d].__TABLES__ \u3092\u53c2\u7167\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n\n\n\n<p>\u4e0b\u8a18\u306e\u3088\u3046\u306a\u611f\u3058\u3067\u3001\u30b5\u30a4\u30ba\u304c\u5927\u304d\u3044\u30c7\u30fc\u30bf\u30bb\u30c3\u30c8\u3084\u30c6\u30fc\u30d6\u30eb\u306e\u78ba\u8a8d\u304c\u3067\u304d\u308b\u304b\u3068\u601d\u3063\u3066\u3044\u305f\u306e\u3067\u3059\u304c\u3001\u3067\u304d\u306a\u3044\u3088\u3046\u3067\u3059\u3002\u6b8b\u5ff5\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT dataset_id, sum(size_bytes) INFORMATION_SCHEMA group by dataset_id ORDER BY sum(size_bytes) DESC<\/code><\/pre>\n\n\n\n<h2><span id=\"ALTER_TABLE\">ALTER TABLE<\/span><\/h2>\n\n\n\n<p>ALTER TABLE \u306f\u4f5c\u6210\u6e08\u30c6\u30fc\u30d6\u30eb\u306e\u60c5\u5831\u3092\u66f4\u65b0\u3059\u308b DDL \u6587\u3067\u3059\u3002<\/p>\n\n\n\n<p>MySQL\u30fbPostgreSQL\u30fbOracle\u306e\u3088\u3046\u306b\u3001\u4f5c\u6210\u6e08\u30c6\u30fc\u30d6\u30eb\u540d\u5909\u66f4\u30fb\u30ab\u30e9\u30e0\u8ffd\u52a0\u30fb\u578b\u5909\u66f4\u304c\u3067\u304d\u308b\u3088\u3046\u306b\u306a\u3063\u305f\u304b\u3068\u671f\u5f85\u3057\u305f\u306e\u3067\u3059\u304c\u3001\u6b8b\u5ff5\u306a\u304c\u3089\u305d\u306e\u3088\u3046\u306a\u3082\u306e\u3067\u306f\u3042\u308a\u307e\u305b\u3093\u3067\u3057\u305f\u3002<\/p>\n\n\n\n<p>\u8a2d\u5b9a\u3067\u304d\u308b\u306e\u306f\u4e0b\u8a18\u3067\u3059\u3002\u304b\u306a\u308a\u9650\u5b9a\u3055\u308c\u3066\u3044\u307e\u3059\u306d\u3002<\/p>\n\n\n\n<ul><li>\u30c6\u30fc\u30d6\u30eb\u30fb\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u306e\u6709\u52b9\u671f\u9650 (expiration) \u8a2d\u5b9a<\/li><li>\u5fc5\u9808\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u30d5\u30a3\u30eb\u30bf (required partition filter) \u8a2d\u5b9a<\/li><li>KMS\u30ad\u30fc\u540d\u8a2d\u5b9a<\/li><li>\u8aac\u660e\u6587 (description) \u8a2d\u5b9a<\/li><li>\u30e9\u30d9\u30eb\u8a2d\u5b9a<\/li><\/ul>\n\n\n\n<p>\u5177\u4f53\u7684\u306b\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306b ALTER TABLE \u6587\u3092\u5b9f\u884c\u3057\u307e\u3059\u3002\u4e0b\u8a18\u306f\u30c6\u30fc\u30d6\u30eb\u306b\u6709\u52b9\u671f\u9650\u3092\u8a2d\u5b9a\u3057\u3001\u8aac\u660e\u6587\u3092\u66f4\u65b0\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> #standardSQL\n ALTER TABLE mydataset.mytable\n SET OPTIONS (\n   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),\n   description=\"Table that expires seven days from now\"\n )<\/code><\/pre>\n\n\n\n<p>\u8a73\u7d30\u60c5\u5831\u306f\u4e0b\u8a18\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n\n\n\n<p><a href=\"https:\/\/cloud.google.com\/bigquery\/docs\/reference\/standard-sql\/data-definition-language\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\" (\u65b0\u3057\u3044\u30bf\u30d6\u3067\u958b\u304f)\">https:\/\/cloud.google.com\/bigquery\/docs\/reference\/standard-sql\/data-definition-language<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>2019\/02\/04 \u306b GCP \u306e BigQuery \u306b\u3001\u30e1\u30bf\u60c5\u5831 INFORMATION_SCHEMA\u3068\u3001ALTER TABLE\u304c\u8ffd\u52a0\u3055\u308c\u307e\u3057\u305f\u3002 \u76ee\u6b211 INFORMATION_SCHEMA1.1 INFORMATION_SCHEMA \u306e\u6599\u91d11.2 \u30c6\u30fc\u30d6\u30eb\u306e\u884c\u6570\u3068\u30b5\u30a4\u30ba2 ALTER TABLE INFORMATION_SCHEMA \u30c7\u30fc\u30bf\u30bb\u30c3\u30c8\u4e00\u89a7\u3092\u53d6\u5f97\u3059\u308b\u306b\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306b\u3057\u307e\u3059\u3002 \u7d50\u679c\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002 \u30c6\u30fc\u30d6\u30eb\u4e00\u89a7\u3092\u53d6\u5f97\u3059\u308b\u306b\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306b\u3057\u307e\u3059\u3002 \u30ab\u30e9\u30e0\u60c5\u5831\u53d6\u5f97\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306b\u3057\u307e\u3059\u3002 \u7d50\u679c\u306f\u4e0b\u8a18\u3067\u3059\u3002 \u3055\u3089\u306a\u308b\u8a73\u7d30\u306f\u4e0b\u8a18\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002 https:\/\/cloud.google.com\/bigquery\/docs\/information-schema-intro INFORMATION_SCHEMA \u306e\u6599\u91d1 INFORMATION_SCHEMA\u304b\u3089\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3059\u308b\u3068\u3001\u6700\u4f4e\u3067\u308210MB\u5206\u306e\u30af\u30a8\u30ea\u30b3\u30b9\u30c8\u304c\u304b\u304b\u308a\u307e\u3059\u3002\u306a\u305c\u306a\u3089\u30ad\u30e3\u30c3\u30b7\u30e5\u3055\u308c\u306a\u3044\u304b\u3089\u3060\u305d\u3046\u3067\u3059\u304c\u3001\u30e1\u30bf\u60c5\u5831\u3067\u304a\u91d1\u53d6\u308b\u3093\u304b\u3044! \u3063\u3066\u611f\u3058\u3067\u3059\u3002\u307e\u300110MB\u5206\u30670.00022\u5186\u3067\u3059\u3051\u3069\u306d\u3002 \u30c6\u30fc\u30d6\u30eb\u306e\u884c\u6570\u3068\u30b5\u30a4\u30ba INFORMATION_SCHEMA \u306b\u3001\u30c6\u30fc\u30d6\u30eb\u306e\u884c\u6570\u3084\u30b5\u30a4\u30ba\u60c5\u5831\u306f\u542b\u307e\u308c\u3066\u3044\u307e\u305b\u3093\u3002\u3053\u308c\u307e\u3067\u901a\u308a\u30e1\u30bf\u30c6\u30fc\u30d6\u30eb [\u30c7\u30fc\u30bf\u30bb\u30c3\u30c8\u540d].__TABLES__ \u3092\u53c2\u7167\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002 \u4e0b\u8a18\u306e\u3088\u3046\u306a\u611f\u3058\u3067\u3001\u30b5\u30a4\u30ba\u304c\u5927\u304d\u3044\u30c7\u30fc\u30bf\u30bb\u30c3\u30c8\u3084\u30c6\u30fc\u30d6\u30eb\u306e\u78ba\u8a8d\u304c\u3067\u304d\u308b\u304b\u3068\u601d\u3063\u3066\u3044\u305f\u306e\u3067\u3059\u304c\u3001\u3067\u304d\u306a\u3044\u3088\u3046\u3067\u3059\u3002\u6b8b\u5ff5\u3002 ALTER TABLE ALTER TABLE \u306f\u4f5c\u6210\u6e08\u30c6\u30fc\u30d6\u30eb\u306e\u60c5\u5831\u3092\u66f4\u65b0\u3059\u308b DDL \u6587\u3067\u3059\u3002 MySQL\u30fbPostgreSQL\u30fbOracle\u306e\u3088\u3046\u306b\u3001\u4f5c\u6210\u6e08\u30c6\u30fc\u30d6\u30eb\u540d\u5909\u66f4\u30fb\u30ab\u30e9\u30e0\u8ffd\u52a0\u30fb\u578b\u5909\u66f4\u304c\u3067\u304d\u308b\u3088\u3046\u306b\u306a\u3063\u305f\u304b\u3068\u671f\u5f85\u3057\u305f\u306e\u3067\u3059\u304c\u3001\u6b8b\u5ff5\u306a\u304c\u3089\u305d\u306e\u3088\u3046\u306a\u3082\u306e\u3067\u306f\u3042\u308a\u307e\u305b\u3093\u3067\u3057\u305f\u3002 \u8a2d\u5b9a\u3067\u304d\u308b\u306e\u306f\u4e0b\u8a18\u3067\u3059\u3002\u304b\u306a\u308a\u9650\u5b9a\u3055\u308c\u3066\u3044\u307e\u3059\u306d\u3002 \u30c6\u30fc\u30d6\u30eb\u30fb\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u306e\u6709\u52b9\u671f\u9650 (expiration) \u8a2d\u5b9a \u5fc5\u9808\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u30d5\u30a3\u30eb\u30bf (required partition filter) \u8a2d\u5b9a KMS\u30ad\u30fc\u540d\u8a2d\u5b9a \u8aac\u660e\u6587 (description) \u8a2d\u5b9a \u30e9\u30d9\u30eb\u8a2d\u5b9a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[10],"tags":[],"_links":{"self":[{"href":"https:\/\/cloud-textbook.com\/wp-json\/wp\/v2\/posts\/760"}],"collection":[{"href":"https:\/\/cloud-textbook.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cloud-textbook.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cloud-textbook.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cloud-textbook.com\/wp-json\/wp\/v2\/comments?post=760"}],"version-history":[{"count":1,"href":"https:\/\/cloud-textbook.com\/wp-json\/wp\/v2\/posts\/760\/revisions"}],"predecessor-version":[{"id":872,"href":"https:\/\/cloud-textbook.com\/wp-json\/wp\/v2\/posts\/760\/revisions\/872"}],"wp:attachment":[{"href":"https:\/\/cloud-textbook.com\/wp-json\/wp\/v2\/media?parent=760"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cloud-textbook.com\/wp-json\/wp\/v2\/categories?post=760"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cloud-textbook.com\/wp-json\/wp\/v2\/tags?post=760"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}