search

sinmetal / til

Today I Learned
MIT License
0 stars 0 forks source link

partitioned table expiration #42

Open sinmetal opened 1 year ago

sinmetal commented 1 year ago

default expirationなしでdataset作成

{
  "kind": "bigquery#dataset",
  "etag": "0RCXkvY+5Mg7WkFL9M+twA==",
  "id": "terraform20230306:first_dataset",
  "selfLink": "https://content-bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset",
  "datasetReference": {
    "datasetId": "first_dataset",
    "projectId": "terraform20230306"
  },
  "friendlyName": "test",
  "description": "This is a test description",
  "access": [
    {
      "role": "WRITER",
      "specialGroup": "projectWriters"
    },
    {
      "role": "OWNER",
      "specialGroup": "projectOwners"
    },
    {
      "role": "OWNER",
      "userByEmail": "915591290080@cloudbuild.gserviceaccount.com"
    },
    {
      "role": "READER",
      "specialGroup": "projectReaders"
    }
  ],
  "creationTime": "1678080842742",
  "lastModifiedTime": "1678080842742",
  "location": "US",
  "type": "DEFAULT"
}
sinmetal commented 1 year ago

table作成時はexpirationはなし

resource "google_bigquery_table" "bar" {
  project    = "terraform20230306"
  dataset_id = google_bigquery_dataset.first_dataset.dataset_id
  table_id   = "bar"

  time_partitioning {
    type = "DAY"
  }

  schema = <<EOF
[
  {
    "name": "permalink",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "The Permalink"
  },
  {
    "name": "state",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "State where the head office is located"
  }
]
EOF

}
{
  "kind": "bigquery#table",
  "etag": "I/agMS61t/nL2wpwzOFh+w==",
  "id": "terraform20230306:first_dataset.bar",
  "selfLink": "https://content-bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset/tables/bar",
  "tableReference": {
    "projectId": "terraform20230306",
    "datasetId": "first_dataset",
    "tableId": "bar"
  },
  "schema": {
    "fields": [
      {
        "name": "permalink",
        "type": "STRING",
        "mode": "NULLABLE",
        "description": "The Permalink"
      },
      {
        "name": "state",
        "type": "STRING",
        "mode": "NULLABLE",
        "description": "State where the head office is located"
      }
    ]
  },
  "timePartitioning": {
    "type": "DAY"
  },
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "creationTime": "1678081115020",
  "lastModifiedTime": "1678081115126",
  "type": "TABLE",
  "location": "US",
  "numTotalLogicalBytes": "0",
  "numActiveLogicalBytes": "0",
  "numLongTermLogicalBytes": "0"
}
sinmetal commented 1 year ago

datasetにdefault_partition_expiration_msを設定

Terraform will perform the following actions:

  # google_bigquery_dataset.first_dataset will be updated in-place
  ~ resource "google_bigquery_dataset" "first_dataset" {
      ~ default_partition_expiration_ms = 0 -> 864000000
        id                              = "projects/terraform20230306/datasets/first_dataset"
        # (12 unchanged attributes hidden)

        # (4 unchanged blocks hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.
google_bigquery_dataset.first_dataset: Modifying... [id=projects/terraform20230306/datasets/first_dataset]
google_bigquery_dataset.first_dataset: Modifications complete after 0s [id=projects/terraform20230306/datasets/first_dataset]
{
  "kind": "bigquery#dataset",
  "etag": "mxZQ4yY/fr0trfvkZTQ0oA==",
  "id": "terraform20230306:first_dataset",
  "selfLink": "https://content-bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset",
  "datasetReference": {
    "datasetId": "first_dataset",
    "projectId": "terraform20230306"
  },
  "friendlyName": "test",
  "description": "This is a test description",
  "access": [
    {
      "role": "WRITER",
      "specialGroup": "projectWriters"
    },
    {
      "role": "OWNER",
      "specialGroup": "projectOwners"
    },
    {
      "role": "OWNER",
      "userByEmail": "915591290080@cloudbuild.gserviceaccount.com"
    },
    {
      "role": "READER",
      "specialGroup": "projectReaders"
    }
  ],
  "creationTime": "1678080842742",
  "lastModifiedTime": "1678082085816",
  "location": "US",
  "defaultPartitionExpirationMs": "864000000",
  "type": "DEFAULT"
}

既存のtableには変化なし

{
  "kind": "bigquery#table",
  "etag": "I/agMS61t/nL2wpwzOFh+w==",
  "id": "terraform20230306:first_dataset.bar",
  "selfLink": "https://content-bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset/tables/bar",
  "tableReference": {
    "projectId": "terraform20230306",
    "datasetId": "first_dataset",
    "tableId": "bar"
  },
  "schema": {
    "fields": [
      {
        "name": "permalink",
        "type": "STRING",
        "mode": "NULLABLE",
        "description": "The Permalink"
      },
      {
        "name": "state",
        "type": "STRING",
        "mode": "NULLABLE",
        "description": "State where the head office is located"
      }
    ]
  },
  "timePartitioning": {
    "type": "DAY"
  },
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "creationTime": "1678081115020",
  "lastModifiedTime": "1678081115126",
  "type": "TABLE",
  "location": "US",
  "numTotalLogicalBytes": "0",
  "numActiveLogicalBytes": "0",
  "numLongTermLogicalBytes": "0"
}
sinmetal commented 1 year ago

既存のbar tableにtimePartitioning.expiration_msを設定すると適用される

tf plan

Terraform will perform the following actions:

  # google_bigquery_table.bar will be updated in-place
  ~ resource "google_bigquery_table" "bar" {
        id                  = "projects/terraform20230306/datasets/first_dataset/tables/bar"
        # (16 unchanged attributes hidden)

      ~ time_partitioning {
          ~ expiration_ms            = 0 -> 864000000
            # (2 unchanged attributes hidden)
        }
    }

Plan: 0 to add, 1 to change, 0 to destroy.

tf apply

Terraform will perform the following actions:

  # google_bigquery_table.bar will be updated in-place
  ~ resource "google_bigquery_table" "bar" {
        id                  = "projects/terraform20230306/datasets/first_dataset/tables/bar"
        # (16 unchanged attributes hidden)

      ~ time_partitioning {
          ~ expiration_ms            = 0 -> 864000000
            # (2 unchanged attributes hidden)
        }
    }

Plan: 0 to add, 1 to change, 0 to destroy.
google_bigquery_table.bar: Modifying... [id=projects/terraform20230306/datasets/first_dataset/tables/bar]
google_bigquery_table.bar: Modifications complete after 1s [id=projects/terraform20230306/datasets/first_dataset/tables/bar]

Apply complete! Resources: 0 added, 1 changed, 0 destroyed.

bar table

{
  "kind": "bigquery#table",
  "etag": "3CP7zmrI8eBT2NtZF/6w1g==",
  "id": "terraform20230306:first_dataset.bar",
  "selfLink": "https://content-bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset/tables/bar",
  "tableReference": {
    "projectId": "terraform20230306",
    "datasetId": "first_dataset",
    "tableId": "bar"
  },
  "schema": {
    "fields": [
      {
        "name": "permalink",
        "type": "STRING",
        "mode": "NULLABLE",
        "description": "The Permalink"
      },
      {
        "name": "state",
        "type": "STRING",
        "mode": "NULLABLE",
        "description": "State where the head office is located"
      }
    ]
  },
  "timePartitioning": {
    "type": "DAY",
    "expirationMs": "864000000"
  },
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "creationTime": "1678081115020",
  "lastModifiedTime": "1678082433492",
  "type": "TABLE",
  "location": "US",
  "numTotalLogicalBytes": "0",
  "numActiveLogicalBytes": "0",
  "numLongTermLogicalBytes": "0"
}
sinmetal commented 1 year ago

expirationを設定して新しいtableを作ると、それにはdatasetのdefaultは適用されない 10day -> 1dayにしてるので、ちょっと分かりにくいが、datasetのdefaultは864000000で、tableは86400000

tf plan

Terraform will perform the following actions:

  # google_bigquery_table.foo will be created
  + resource "google_bigquery_table" "foo" {
      + creation_time       = (known after apply)
      + dataset_id          = "first_dataset"
      + deletion_protection = true
      + etag                = (known after apply)
      + expiration_time     = (known after apply)
      + id                  = (known after apply)
      + last_modified_time  = (known after apply)
      + location            = (known after apply)
      + num_bytes           = (known after apply)
      + num_long_term_bytes = (known after apply)
      + num_rows            = (known after apply)
      + project             = "terraform20230306"
      + schema              = jsonencode(
            [
              + {
                  + description = "The Permalink"
                  + mode        = "NULLABLE"
                  + name        = "permalink"
                  + type        = "STRING"
                },
              + {
                  + description = "State where the head office is located"
                  + mode        = "NULLABLE"
                  + name        = "state"
                  + type        = "STRING"
                },
            ]
        )
      + self_link           = (known after apply)
      + table_id            = "foo"
      + type                = (known after apply)

      + time_partitioning {
          + expiration_ms = 86400000
          + type          = "DAY"
        }
    }

Plan: 1 to add, 0 to change, 0 to destroy.

tf apply

Terraform will perform the following actions:

  # google_bigquery_table.foo will be created
  + resource "google_bigquery_table" "foo" {
      + creation_time       = (known after apply)
      + dataset_id          = "first_dataset"
      + deletion_protection = true
      + etag                = (known after apply)
      + expiration_time     = (known after apply)
      + id                  = (known after apply)
      + last_modified_time  = (known after apply)
      + location            = (known after apply)
      + num_bytes           = (known after apply)
      + num_long_term_bytes = (known after apply)
      + num_rows            = (known after apply)
      + project             = "terraform20230306"
      + schema              = jsonencode(
            [
              + {
                  + description = "The Permalink"
                  + mode        = "NULLABLE"
                  + name        = "permalink"
                  + type        = "STRING"
                },
              + {
                  + description = "State where the head office is located"
                  + mode        = "NULLABLE"
                  + name        = "state"
                  + type        = "STRING"
                },
            ]
        )
      + self_link           = (known after apply)
      + table_id            = "foo"
      + type                = (known after apply)

      + time_partitioning {
          + expiration_ms = 86400000
          + type          = "DAY"
        }
    }

Plan: 1 to add, 0 to change, 0 to destroy.
google_bigquery_table.foo: Creating...
google_bigquery_table.foo: Creation complete after 0s [id=projects/terraform20230306/datasets/first_dataset/tables/foo]

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

table

{
  "kind": "bigquery#table",
  "etag": "15Zr/5U5efS6odrTyo+PLg==",
  "id": "terraform20230306:first_dataset.foo",
  "selfLink": "https://content-bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset/tables/foo",
  "tableReference": {
    "projectId": "terraform20230306",
    "datasetId": "first_dataset",
    "tableId": "foo"
  },
  "schema": {
    "fields": [
      {
        "name": "permalink",
        "type": "STRING",
        "mode": "NULLABLE",
        "description": "The Permalink"
      },
      {
        "name": "state",
        "type": "STRING",
        "mode": "NULLABLE",
        "description": "State where the head office is located"
      }
    ]
  },
  "timePartitioning": {
    "type": "DAY",
    "expirationMs": "86400000"
  },
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "creationTime": "1678083385013",
  "lastModifiedTime": "1678083385141",
  "type": "TABLE",
  "location": "US",
  "numTotalLogicalBytes": "0",
  "numActiveLogicalBytes": "0",
  "numLongTermLogicalBytes": "0"
}
sinmetal commented 1 year ago

expirationを明示的に指定していない新しいtableを作成

tf plan

Terraform will perform the following actions:

  # google_bigquery_table.hoge will be created
  + resource "google_bigquery_table" "hoge" {
      + creation_time       = (known after apply)
      + dataset_id          = "first_dataset"
      + deletion_protection = true
      + etag                = (known after apply)
      + expiration_time     = (known after apply)
      + id                  = (known after apply)
      + last_modified_time  = (known after apply)
      + location            = (known after apply)
      + num_bytes           = (known after apply)
      + num_long_term_bytes = (known after apply)
      + num_rows            = (known after apply)
      + project             = "terraform20230306"
      + schema              = jsonencode(
            [
              + {
                  + description = "The Permalink"
                  + mode        = "NULLABLE"
                  + name        = "permalink"
                  + type        = "STRING"
                },
              + {
                  + description = "State where the head office is located"
                  + mode        = "NULLABLE"
                  + name        = "state"
                  + type        = "STRING"
                },
            ]
        )
      + self_link           = (known after apply)
      + table_id            = "hoge"
      + type                = (known after apply)

      + time_partitioning {
          + expiration_ms = (known after apply)
          + type          = "DAY"
        }
    }

Plan: 1 to add, 0 to change, 0 to destroy.

tf apply

Terraform will perform the following actions:

  # google_bigquery_table.hoge will be created
  + resource "google_bigquery_table" "hoge" {
      + creation_time       = (known after apply)
      + dataset_id          = "first_dataset"
      + deletion_protection = true
      + etag                = (known after apply)
      + expiration_time     = (known after apply)
      + id                  = (known after apply)
      + last_modified_time  = (known after apply)
      + location            = (known after apply)
      + num_bytes           = (known after apply)
      + num_long_term_bytes = (known after apply)
      + num_rows            = (known after apply)
      + project             = "terraform20230306"
      + schema              = jsonencode(
            [
              + {
                  + description = "The Permalink"
                  + mode        = "NULLABLE"
                  + name        = "permalink"
                  + type        = "STRING"
                },
              + {
                  + description = "State where the head office is located"
                  + mode        = "NULLABLE"
                  + name        = "state"
                  + type        = "STRING"
                },
            ]
        )
      + self_link           = (known after apply)
      + table_id            = "hoge"
      + type                = (known after apply)

      + time_partitioning {
          + expiration_ms = (known after apply)
          + type          = "DAY"
        }
    }

Plan: 1 to add, 0 to change, 0 to destroy.
google_bigquery_table.hoge: Creating...
google_bigquery_table.hoge: Creation complete after 1s [id=projects/terraform20230306/datasets/first_dataset/tables/hoge]

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

tableにはdatasetのexpirationが設定されている

{
  "kind": "bigquery#table",
  "etag": "QcbkJmPOarxnI4qr1EWwxw==",
  "id": "terraform20230306:first_dataset.hoge",
  "selfLink": "https://content-bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset/tables/hoge",
  "tableReference": {
    "projectId": "terraform20230306",
    "datasetId": "first_dataset",
    "tableId": "hoge"
  },
  "schema": {
    "fields": [
      {
        "name": "permalink",
        "type": "STRING",
        "mode": "NULLABLE",
        "description": "The Permalink"
      },
      {
        "name": "state",
        "type": "STRING",
        "mode": "NULLABLE",
        "description": "State where the head office is located"
      }
    ]
  },
  "timePartitioning": {
    "type": "DAY",
    "expirationMs": "864000000"
  },
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "creationTime": "1678083939503",
  "lastModifiedTime": "1678083939628",
  "type": "TABLE",
  "location": "US",
  "numTotalLogicalBytes": "0",
  "numActiveLogicalBytes": "0",
  "numLongTermLogicalBytes": "0"
}

tf state上もtimePartitioning.expirationMsは入っている

{
  "version": 4,
  "terraform_version": "1.3.9",
  "serial": 9,
  "lineage": "0ab290d5-51bb-06ce-3a93-352e8098c063",
  "outputs": {},
  "resources": [
    {
      "mode": "managed",
      "type": "google_bigquery_dataset",
      "name": "first_dataset",
      "provider": "provider[\"registry.terraform.io/hashicorp/google\"]",
      "instances": [
        {
          "schema_version": 0,
          "attributes": {
            "access": [
              {
                "dataset": [],
                "domain": "",
                "group_by_email": "",
                "role": "OWNER",
                "routine": [],
                "special_group": "",
                "user_by_email": "915591290080@cloudbuild.gserviceaccount.com",
                "view": []
              },
              {
                "dataset": [],
                "domain": "",
                "group_by_email": "",
                "role": "OWNER",
                "routine": [],
                "special_group": "projectOwners",
                "user_by_email": "",
                "view": []
              },
              {
                "dataset": [],
                "domain": "",
                "group_by_email": "",
                "role": "READER",
                "routine": [],
                "special_group": "projectReaders",
                "user_by_email": "",
                "view": []
              },
              {
                "dataset": [],
                "domain": "",
                "group_by_email": "",
                "role": "WRITER",
                "routine": [],
                "special_group": "projectWriters",
                "user_by_email": "",
                "view": []
              }
            ],
            "creation_time": 1678080842742,
            "dataset_id": "first_dataset",
            "default_encryption_configuration": [],
            "default_partition_expiration_ms": 864000000,
            "default_table_expiration_ms": 0,
            "delete_contents_on_destroy": false,
            "description": "This is a test description",
            "etag": "mxZQ4yY/fr0trfvkZTQ0oA==",
            "friendly_name": "test",
            "id": "projects/terraform20230306/datasets/first_dataset",
            "labels": {},
            "last_modified_time": 1678082085816,
            "location": "US",
            "max_time_travel_hours": "",
            "project": "terraform20230306",
            "self_link": "https://bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset",
            "timeouts": null
          },
          "sensitive_attributes": [],
          "private": "eyJlMmJmYjczMC1lY2FhLTExZTYtOGY4OC0zNDM2M2JjN2M0YzAiOnsiY3JlYXRlIjoxMjAwMDAwMDAwMDAwLCJkZWxldGUiOjEyMDAwMDAwMDAwMDAsInVwZGF0ZSI6MTIwMDAwMDAwMDAwMH19"
        }
      ]
    },
    {
      "mode": "managed",
      "type": "google_bigquery_table",
      "name": "bar",
      "provider": "provider[\"registry.terraform.io/hashicorp/google\"]",
      "instances": [
        {
          "schema_version": 0,
          "attributes": {
            "clustering": null,
            "creation_time": 1678081115020,
            "dataset_id": "first_dataset",
            "deletion_protection": true,
            "description": "",
            "encryption_configuration": [],
            "etag": "3CP7zmrI8eBT2NtZF/6w1g==",
            "expiration_time": 0,
            "external_data_configuration": [],
            "friendly_name": "",
            "id": "projects/terraform20230306/datasets/first_dataset/tables/bar",
            "labels": {},
            "last_modified_time": 1678082433492,
            "location": "US",
            "materialized_view": [],
            "num_bytes": 0,
            "num_long_term_bytes": 0,
            "num_rows": 0,
            "project": "terraform20230306",
            "range_partitioning": [],
            "schema": "[{\"description\":\"The Permalink\",\"mode\":\"NULLABLE\",\"name\":\"permalink\",\"type\":\"STRING\"},{\"description\":\"State where the head office is located\",\"mode\":\"NULLABLE\",\"name\":\"state\",\"type\":\"STRING\"}]",
            "self_link": "https://bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset/tables/bar",
            "table_id": "bar",
            "time_partitioning": [
              {
                "expiration_ms": 864000000,
                "field": "",
                "require_partition_filter": false,
                "type": "DAY"
              }
            ],
            "type": "TABLE",
            "view": []
          },
          "sensitive_attributes": [],
          "private": "bnVsbA==",
          "dependencies": [
            "google_bigquery_dataset.first_dataset"
          ]
        }
      ]
    },
    {
      "mode": "managed",
      "type": "google_bigquery_table",
      "name": "foo",
      "provider": "provider[\"registry.terraform.io/hashicorp/google\"]",
      "instances": [
        {
          "schema_version": 0,
          "attributes": {
            "clustering": null,
            "creation_time": 1678083385013,
            "dataset_id": "first_dataset",
            "deletion_protection": true,
            "description": "",
            "encryption_configuration": [],
            "etag": "15Zr/5U5efS6odrTyo+PLg==",
            "expiration_time": 0,
            "external_data_configuration": [],
            "friendly_name": "",
            "id": "projects/terraform20230306/datasets/first_dataset/tables/foo",
            "labels": {},
            "last_modified_time": 1678083385141,
            "location": "US",
            "materialized_view": [],
            "num_bytes": 0,
            "num_long_term_bytes": 0,
            "num_rows": 0,
            "project": "terraform20230306",
            "range_partitioning": [],
            "schema": "[{\"description\":\"The Permalink\",\"mode\":\"NULLABLE\",\"name\":\"permalink\",\"type\":\"STRING\"},{\"description\":\"State where the head office is located\",\"mode\":\"NULLABLE\",\"name\":\"state\",\"type\":\"STRING\"}]",
            "self_link": "https://bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset/tables/foo",
            "table_id": "foo",
            "time_partitioning": [
              {
                "expiration_ms": 86400000,
                "field": "",
                "require_partition_filter": false,
                "type": "DAY"
              }
            ],
            "type": "TABLE",
            "view": []
          },
          "sensitive_attributes": [],
          "private": "bnVsbA==",
          "dependencies": [
            "google_bigquery_dataset.first_dataset"
          ]
        }
      ]
    },
    {
      "mode": "managed",
      "type": "google_bigquery_table",
      "name": "hoge",
      "provider": "provider[\"registry.terraform.io/hashicorp/google\"]",
      "instances": [
        {
          "schema_version": 0,
          "attributes": {
            "clustering": null,
            "creation_time": 1678083939503,
            "dataset_id": "first_dataset",
            "deletion_protection": true,
            "description": "",
            "encryption_configuration": [],
            "etag": "QcbkJmPOarxnI4qr1EWwxw==",
            "expiration_time": 0,
            "external_data_configuration": [],
            "friendly_name": "",
            "id": "projects/terraform20230306/datasets/first_dataset/tables/hoge",
            "labels": null,
            "last_modified_time": 1678083939628,
            "location": "US",
            "materialized_view": [],
            "num_bytes": 0,
            "num_long_term_bytes": 0,
            "num_rows": 0,
            "project": "terraform20230306",
            "range_partitioning": [],
            "schema": "[{\"description\":\"The Permalink\",\"mode\":\"NULLABLE\",\"name\":\"permalink\",\"type\":\"STRING\"},{\"description\":\"State where the head office is located\",\"mode\":\"NULLABLE\",\"name\":\"state\",\"type\":\"STRING\"}]",
            "self_link": "https://bigquery.googleapis.com/bigquery/v2/projects/terraform20230306/datasets/first_dataset/tables/hoge",
            "table_id": "hoge",
            "time_partitioning": [
              {
                "expiration_ms": 864000000,
                "field": "",
                "require_partition_filter": false,
                "type": "DAY"
              }
            ],
            "type": "TABLE",
            "view": []
          },
          "sensitive_attributes": [],
          "private": "bnVsbA==",
          "dependencies": [
            "google_bigquery_dataset.first_dataset"
          ]
        }
      ]
    }
  ],
  "check_results": null
}