Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit57ffc66

Browse files
authored
feat: add support for materialized views (#408)
Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly:- [x] Make sure to open an issue as a [bug/issue](https://github.com/googleapis/python-bigquery/issues/new/choose) before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea- [x] Ensure the tests and linter pass- [x] Code coverage does not decrease (if any source code was changed)- [x] Appropriate docs were updated (if necessary)Fixes#407 🦕
1 parentc384b45 commit57ffc66

File tree

4 files changed

+317
-18
lines changed

4 files changed

+317
-18
lines changed

‎google/cloud/bigquery/table.py‎

Lines changed: 83 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -293,15 +293,18 @@ class Table(object):
293293
"""
294294

295295
_PROPERTY_TO_API_FIELD= {
296-
"friendly_name":"friendlyName",
296+
"encryption_configuration":"encryptionConfiguration",
297297
"expires":"expirationTime",
298-
"time_partitioning":"timePartitioning",
299-
"partitioning_type":"timePartitioning",
298+
"external_data_configuration":"externalDataConfiguration",
299+
"friendly_name":"friendlyName",
300+
"mview_enable_refresh":"materializedView",
301+
"mview_query":"materializedView",
302+
"mview_refresh_interval":"materializedView",
300303
"partition_expiration":"timePartitioning",
304+
"partitioning_type":"timePartitioning",
305+
"time_partitioning":"timePartitioning",
301306
"view_use_legacy_sql":"view",
302307
"view_query":"view",
303-
"external_data_configuration":"externalDataConfiguration",
304-
"encryption_configuration":"encryptionConfiguration",
305308
"require_partition_filter":"requirePartitionFilter",
306309
}
307310

@@ -714,18 +717,14 @@ def view_query(self):
714717
Raises:
715718
ValueError: For invalid value types.
716719
"""
717-
view=self._properties.get("view")
718-
ifviewisnotNone:
719-
returnview.get("query")
720+
return_helpers._get_sub_prop(self._properties, ["view","query"])
720721

721722
@view_query.setter
722723
defview_query(self,value):
723724
ifnotisinstance(value,six.string_types):
724725
raiseValueError("Pass a string")
725-
view=self._properties.get("view")
726-
ifviewisNone:
727-
view=self._properties["view"]= {}
728-
view["query"]=value
726+
_helpers._set_sub_prop(self._properties, ["view","query"],value)
727+
view=self._properties["view"]
729728
# The service defaults useLegacySql to True, but this
730729
# client uses Standard SQL by default.
731730
ifview.get("useLegacySql")isNone:
@@ -746,6 +745,78 @@ def view_use_legacy_sql(self, value):
746745
self._properties["view"]= {}
747746
self._properties["view"]["useLegacySql"]=value
748747

748+
@property
749+
defmview_query(self):
750+
"""Optional[str]: SQL query defining the table as a materialized
751+
view (defaults to :data:`None`).
752+
"""
753+
return_helpers._get_sub_prop(self._properties, ["materializedView","query"])
754+
755+
@mview_query.setter
756+
defmview_query(self,value):
757+
_helpers._set_sub_prop(
758+
self._properties, ["materializedView","query"],str(value)
759+
)
760+
761+
@mview_query.deleter
762+
defmview_query(self):
763+
"""Delete SQL query defining the table as a materialized view."""
764+
self._properties.pop("materializedView",None)
765+
766+
@property
767+
defmview_last_refresh_time(self):
768+
"""Optional[datetime.datetime]: Datetime at which the materialized view was last
769+
refreshed (:data:`None` until set from the server).
770+
"""
771+
refresh_time=_helpers._get_sub_prop(
772+
self._properties, ["materializedView","lastRefreshTime"]
773+
)
774+
ifrefresh_timeisnotNone:
775+
# refresh_time will be in milliseconds.
776+
returngoogle.cloud._helpers._datetime_from_microseconds(
777+
1000*int(refresh_time)
778+
)
779+
780+
@property
781+
defmview_enable_refresh(self):
782+
"""Optional[bool]: Enable automatic refresh of the materialized view
783+
when the base table is updated. The default value is :data:`True`.
784+
"""
785+
return_helpers._get_sub_prop(
786+
self._properties, ["materializedView","enableRefresh"]
787+
)
788+
789+
@mview_enable_refresh.setter
790+
defmview_enable_refresh(self,value):
791+
return_helpers._set_sub_prop(
792+
self._properties, ["materializedView","enableRefresh"],value
793+
)
794+
795+
@property
796+
defmview_refresh_interval(self):
797+
"""Optional[datetime.timedelta]: The maximum frequency at which this
798+
materialized view will be refreshed. The default value is 1800000
799+
milliseconds (30 minutes).
800+
"""
801+
refresh_interval=_helpers._get_sub_prop(
802+
self._properties, ["materializedView","refreshIntervalMs"]
803+
)
804+
ifrefresh_intervalisnotNone:
805+
returndatetime.timedelta(milliseconds=int(refresh_interval))
806+
807+
@mview_refresh_interval.setter
808+
defmview_refresh_interval(self,value):
809+
ifvalueisNone:
810+
refresh_interval_ms=None
811+
else:
812+
refresh_interval_ms=str(value//datetime.timedelta(milliseconds=1))
813+
814+
_helpers._set_sub_prop(
815+
self._properties,
816+
["materializedView","refreshIntervalMs"],
817+
refresh_interval_ms,
818+
)
819+
749820
@property
750821
defstreaming_buffer(self):
751822
"""google.cloud.bigquery.StreamingBuffer: Information about a table's
Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,86 @@
1+
# Copyright 2020 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
16+
defcreate_materialized_view(override_values={}):
17+
# [START bigquery_create_materialized_view]
18+
fromgoogle.cloudimportbigquery
19+
20+
bigquery_client=bigquery.Client()
21+
22+
view_id="my-project.my_dataset.my_materialized_view"
23+
base_table_id="my-project.my_dataset.my_base_table"
24+
# [END bigquery_create_materialized_view]
25+
# To facilitate testing, we replace values with alternatives
26+
# provided by the testing harness.
27+
view_id=override_values.get("view_id",view_id)
28+
base_table_id=override_values.get("base_table_id",view_id)
29+
# [START bigquery_create_materialized_view]
30+
view=bigquery.Table(view_id)
31+
view.mview_query=f"""
32+
SELECT product_id, SUM(clicks) AS sum_clicks
33+
FROM `{base_table_id}`
34+
GROUP BY 1
35+
"""
36+
37+
# Make an API request to create the materialized view.
38+
view=bigquery_client.create_table(view)
39+
print(f"Created{view.table_type}:{str(view.reference)}")
40+
# [END bigquery_create_materialized_view]
41+
returnview
42+
43+
44+
defupdate_materialized_view(override_values={}):
45+
# [START bigquery_update_materialized_view]
46+
importdatetime
47+
fromgoogle.cloudimportbigquery
48+
49+
bigquery_client=bigquery.Client()
50+
51+
view_id="my-project.my_dataset.my_materialized_view"
52+
# [END bigquery_update_materialized_view]
53+
# To facilitate testing, we replace values with alternatives
54+
# provided by the testing harness.
55+
view_id=override_values.get("view_id",view_id)
56+
# [START bigquery_update_materialized_view]
57+
view=bigquery.Table(view_id)
58+
view.mview_enable_refresh=True
59+
view.mview_refresh_interval=datetime.timedelta(hours=1)
60+
61+
# Make an API request to update the materialized view.
62+
view=bigquery_client.update_table(
63+
view,
64+
# Pass in a list of any fields you need to modify.
65+
["mview_enable_refresh","mview_refresh_interval"],
66+
)
67+
print(f"Updated{view.table_type}:{str(view.reference)}")
68+
# [END bigquery_update_materialized_view]
69+
returnview
70+
71+
72+
defdelete_materialized_view(override_values={}):
73+
# [START bigquery_delete_materialized_view]
74+
fromgoogle.cloudimportbigquery
75+
76+
bigquery_client=bigquery.Client()
77+
78+
view_id="my-project.my_dataset.my_materialized_view"
79+
# [END bigquery_delete_materialized_view]
80+
# To facilitate testing, we replace values with alternatives
81+
# provided by the testing harness.
82+
view_id=override_values.get("view_id",view_id)
83+
# [START bigquery_delete_materialized_view]
84+
# Make an API request to delete the materialized view.
85+
bigquery_client.delete_table(view_id)
86+
# [END bigquery_delete_materialized_view]
Lines changed: 93 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,93 @@
1+
# Copyright 2020 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
importdatetime
16+
importuuid
17+
18+
fromgoogle.api_coreimportexceptions
19+
fromgoogle.cloudimportbigquery
20+
importpytest
21+
22+
importmaterialized_view
23+
24+
25+
deftemp_suffix():
26+
returnstr(uuid.uuid4()).replace("-","_")
27+
28+
29+
@pytest.fixture(scope="module")
30+
defbigquery_client():
31+
bigquery_client=bigquery.Client()
32+
returnbigquery_client
33+
34+
35+
@pytest.fixture(autouse=True)
36+
defbigquery_client_patch(monkeypatch,bigquery_client):
37+
monkeypatch.setattr(bigquery,"Client",lambda:bigquery_client)
38+
39+
40+
@pytest.fixture(scope="module")
41+
defproject_id(bigquery_client):
42+
returnbigquery_client.project
43+
44+
45+
@pytest.fixture(scope="module")
46+
defdataset_id(bigquery_client):
47+
dataset_id=f"mvdataset_{temp_suffix()}"
48+
bigquery_client.create_dataset(dataset_id)
49+
yielddataset_id
50+
bigquery_client.delete_dataset(dataset_id,delete_contents=True)
51+
52+
53+
@pytest.fixture(scope="module")
54+
defbase_table_id(bigquery_client,project_id,dataset_id):
55+
base_table_id=f"{project_id}.{dataset_id}.base_{temp_suffix()}"
56+
# Schema from materialized views guide:
57+
# https://cloud.google.com/bigquery/docs/materialized-views#create
58+
base_table=bigquery.Table(base_table_id)
59+
base_table.schema= [
60+
bigquery.SchemaField("product_id",bigquery.SqlTypeNames.INT64),
61+
bigquery.SchemaField("clicks",bigquery.SqlTypeNames.INT64),
62+
]
63+
bigquery_client.create_table(base_table)
64+
yieldbase_table_id
65+
bigquery_client.delete_table(base_table_id)
66+
67+
68+
@pytest.fixture(scope="module")
69+
defview_id(bigquery_client,project_id,dataset_id):
70+
view_id=f"{project_id}.{dataset_id}.mview_{temp_suffix()}"
71+
yieldview_id
72+
bigquery_client.delete_table(view_id,not_found_ok=True)
73+
74+
75+
deftest_materialized_view(capsys,bigquery_client,base_table_id,view_id):
76+
override_values= {
77+
"base_table_id":base_table_id,
78+
"view_id":view_id,
79+
}
80+
view=materialized_view.create_materialized_view(override_values)
81+
assertbase_table_idinview.mview_query
82+
out,_=capsys.readouterr()
83+
assertview_idinout
84+
85+
view=materialized_view.update_materialized_view(override_values)
86+
assertview.mview_enable_refresh
87+
assertview.mview_refresh_interval==datetime.timedelta(hours=1)
88+
out,_=capsys.readouterr()
89+
assertview_idinout
90+
91+
materialized_view.delete_materialized_view(override_values)
92+
withpytest.raises(exceptions.NotFound):
93+
bigquery_client.get_table(view_id)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp