Repository navigation
Expand file tree
/
Copy pathcreate_read_gbq_colab_benchmark_tables.py
More file actions
541 lines (443 loc) · 17.1 KB
/
Copy pathcreate_read_gbq_colab_benchmark_tables.py
File metadata and controls
541 lines (443 loc) · 17.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
# Copyright 2025 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
from __future__ import annotations
import argparse
import base64
import concurrent.futures
import datetime
import json
import math
import time
from typing import Any, Iterable, MutableSequence, Sequence
import numpy as np
from google.cloud import bigquery
# --- Input Data ---
# Generated by querying bigquery-magics usage. See internal issue b/420984164.
TABLE_STATS: dict[str, list[float]] = {
"percentile": [9, 19, 29, 39, 49, 59, 69, 79, 89, 99],
"materialized_or_scanned_bytes": [
0.0,
0.0,
4102.0,
76901.0,
351693.0,
500000.0,
500000.0,
1320930.0,
17486432.0,
1919625975.0,
],
"avg_row_bytes": [
0.00014346299635435792,
0.005370969708923197,
0.3692756731526246,
4.079344721151818,
7.5418,
12.528863516404146,
22.686258546389798,
48.69689224091025,
100.90817356205852,
2020,
],
"materialized_mb": [
0.0,
0.0,
0.004102,
0.076901,
0.351693,
0.5,
0.5,
1.32093,
17.486432,
1919.625975,
],
}
BIGQUERY_DATA_TYPE_SIZES = {
"BOOL": 1,
"DATE": 8,
"FLOAT64": 8,
"INT64": 8,
"DATETIME": 8,
"TIMESTAMP": 8,
"TIME": 8,
"NUMERIC": 16,
# Flexible types.
# JSON base size is its content, BYTES/STRING have 2 byte overhead + content
"JSON": 0,
"BYTES": 2,
"STRING": 2,
}
FIXED_TYPES = [
"BOOL",
"INT64",
"FLOAT64",
"NUMERIC",
"DATE",
"DATETIME",
"TIMESTAMP",
"TIME",
]
FLEXIBLE_TYPES = ["STRING", "BYTES", "JSON"]
JSON_CHAR_LIST = list("abcdef")
STRING_CHAR_LIST = list("abcdefghijklmnopqrstuvwxyz0123456789")
# --- Helper Functions ---
def get_bq_schema(target_row_size_bytes: int) -> Sequence[tuple[str, str, int | None]]:
"""
Determines the BigQuery table schema to match the target_row_size_bytes.
Prioritizes fixed-size types for diversity, then uses flexible types.
Returns a list of tuples: (column_name, type_name, length_for_flexible_type).
Length is None for fixed-size types.
"""
schema: MutableSequence[tuple[str, str, int | None]] = []
current_size = 0
col_idx = 0
for bq_type in FIXED_TYPES:
# For simplicity, we'll allow slight overage if only fixed fields are chosen.
if current_size >= target_row_size_bytes:
break
type_size = BIGQUERY_DATA_TYPE_SIZES[bq_type]
schema.append((f"col_{bq_type.lower()}_{col_idx}", bq_type, None))
current_size += type_size
col_idx += 1
# Use flexible-size types to fill remaining space
# Attempt to add one of each flexible type if space allows
if current_size < target_row_size_bytes:
remaining_bytes_for_content = target_row_size_bytes - current_size
# For simplicity, divide the remaing bytes evenly across the flexible
# columns.
target_size = int(math.ceil(remaining_bytes_for_content / len(FLEXIBLE_TYPES)))
for bq_type in FLEXIBLE_TYPES:
base_cost = BIGQUERY_DATA_TYPE_SIZES[bq_type]
min_content_size = max(0, target_size - base_cost)
schema.append(
(f"col_{bq_type.lower()}_{col_idx}", bq_type, min_content_size)
)
current_size += base_cost + min_content_size
col_idx += 1
return schema
def generate_bool_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
return rng.choice([True, False], size=num_rows)
def generate_int64_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
return rng.integers(-(10**18), 10**18, size=num_rows, dtype=np.int64)
def generate_float64_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
return rng.random(size=num_rows) * 2 * 10**10 - 10**10
def generate_numeric_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
raw_numerics = rng.random(size=num_rows) * 2 * 10**28 - 10**28
format_numeric_vectorized = np.vectorize(lambda x: f"{x:.9f}")
return format_numeric_vectorized(raw_numerics)
def generate_date_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
start_date_ord = datetime.date(1, 1, 1).toordinal()
max_days = (datetime.date(9999, 12, 31) - datetime.date(1, 1, 1)).days
day_offsets = rng.integers(0, max_days + 1, size=num_rows)
date_ordinals = start_date_ord + day_offsets
return np.array(
[
datetime.date.fromordinal(int(ordinal)).isoformat()
for ordinal in date_ordinals
]
)
def generate_numpy_datetimes(num_rows: int, rng: np.random.Generator) -> np.ndarray:
# Generate seconds from a broad range (e.g., year 1 to 9999)
# Note: Python's datetime.timestamp() might be limited by system's C mktime.
# For broader range with np.datetime64, it's usually fine.
# Let's generate epoch seconds relative to Unix epoch for np.datetime64 compatibility
min_epoch_seconds = int(
datetime.datetime(1, 1, 1, 0, 0, 0, tzinfo=datetime.timezone.utc).timestamp()
)
# Max for datetime64[s] is far out, but let's bound it reasonably for BQ.
max_epoch_seconds = int(
datetime.datetime(
9999, 12, 28, 23, 59, 59, tzinfo=datetime.timezone.utc
).timestamp()
)
epoch_seconds = rng.integers(
min_epoch_seconds,
max_epoch_seconds + 1,
size=num_rows,
dtype=np.int64,
)
microseconds_offset = rng.integers(0, 1000000, size=num_rows, dtype=np.int64)
# Create datetime64[s] from epoch seconds and add microseconds as timedelta64[us]
np_timestamps_s = epoch_seconds.astype("datetime64[s]")
np_microseconds_td = microseconds_offset.astype("timedelta64[us]")
return np_timestamps_s + np_microseconds_td
def generate_datetime_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
np_datetimes = generate_numpy_datetimes(num_rows, rng)
# np.datetime_as_string produces 'YYYY-MM-DDTHH:MM:SS.ffffff'
# BQ DATETIME typically uses a space separator: 'YYYY-MM-DD HH:MM:SS.ffffff'
datetime_strings = np.datetime_as_string(np_datetimes, unit="us")
return np.array([s.replace("T", " ") for s in datetime_strings])
def generate_timestamp_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
np_datetimes = generate_numpy_datetimes(num_rows, rng)
# Convert to string with UTC timezone indicator
# np.datetime_as_string with timezone='UTC' produces 'YYYY-MM-DDTHH:MM:SS.ffffffZ'
# BigQuery generally accepts this for TIMESTAMP.
return np.datetime_as_string(np_datetimes, unit="us", timezone="UTC")
def generate_time_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
hours = rng.integers(0, 24, size=num_rows)
minutes = rng.integers(0, 60, size=num_rows)
seconds = rng.integers(0, 60, size=num_rows)
microseconds = rng.integers(0, 1000000, size=num_rows)
time_list = [
datetime.time(hours[i], minutes[i], seconds[i], microseconds[i]).isoformat()
for i in range(num_rows)
]
return np.array(time_list)
def generate_json_row(content_length: int, rng: np.random.Generator) -> str:
json_val_len = max(0, content_length - 5)
json_val_chars = rng.choice(JSON_CHAR_LIST, size=json_val_len)
json_obj = {"k": "".join(json_val_chars)}
return json.dumps(json_obj)
def generate_json_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
content_length = content_length if content_length is not None else 10
json_list = [
generate_json_row(content_length=content_length, rng=rng)
for _ in range(num_rows)
]
return np.array(json_list)
def generate_string_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
content_length = content_length if content_length is not None else 1
content_length = max(0, content_length)
chars_array = rng.choice(STRING_CHAR_LIST, size=(num_rows, content_length))
return np.array(["".join(row_chars) for row_chars in chars_array])
def generate_bytes_batch(
num_rows: int, rng: np.random.Generator, content_length: int | None = None
) -> np.ndarray:
content_length = content_length if content_length is not None else 1
content_length = max(0, content_length)
return np.array(
[
base64.b64encode(rng.bytes(content_length)).decode("utf-8")
for _ in range(num_rows)
]
)
BIGQUERY_DATA_TYPE_GENERATORS = {
"BOOL": generate_bool_batch,
"DATE": generate_date_batch,
"FLOAT64": generate_float64_batch,
"INT64": generate_int64_batch,
"DATETIME": generate_datetime_batch,
"TIMESTAMP": generate_timestamp_batch,
"TIME": generate_time_batch,
"NUMERIC": generate_numeric_batch,
"JSON": generate_json_batch,
"BYTES": generate_bytes_batch,
"STRING": generate_string_batch,
}
def generate_work_items(
table_id: str,
schema: Sequence[tuple[str, str, int | None]],
num_rows: int,
batch_size: int,
) -> Iterable[tuple[str, Sequence[tuple[str, str, int | None]], int]]:
"""
Generates work items of appropriate batch sizes.
"""
if num_rows == 0:
return
generated_rows_total = 0
while generated_rows_total < num_rows:
current_batch_size = min(batch_size, num_rows - generated_rows_total)
if current_batch_size == 0:
break
yield (table_id, schema, current_batch_size)
generated_rows_total += current_batch_size
def generate_batch(
schema: Sequence[tuple[str, str, int | None]],
num_rows: int,
rng: np.random.Generator,
) -> list[dict[str, Any]]:
col_names_ordered = [s[0] for s in schema]
columns_data_batch = {}
for col_name, bq_type, length in schema:
generate_batch = BIGQUERY_DATA_TYPE_GENERATORS[bq_type]
columns_data_batch[col_name] = generate_batch(
num_rows, rng, content_length=length
)
# Turn numpy objects into Python objects.
# https://stackoverflow.com/a/32850511/101923
columns_data_batch_json = {}
for column in columns_data_batch:
columns_data_batch_json[column] = columns_data_batch[column].tolist()
# Assemble batch of rows
batch_data = []
for i in range(num_rows):
row = {
col_name: columns_data_batch_json[col_name][i]
for col_name in col_names_ordered
}
batch_data.append(row)
return batch_data
def generate_and_load_batch(
client: bigquery.Client,
table_id: str,
schema_def: Sequence[tuple[str, str, int | None]],
num_rows: int,
rng: np.random.Generator,
):
bq_schema = []
for col_name, type_name, _ in schema_def:
bq_schema.append(bigquery.SchemaField(col_name, type_name))
table = bigquery.Table(table_id, schema=bq_schema)
generated_data_chunk = generate_batch(schema_def, num_rows, rng)
errors = client.insert_rows_json(table, generated_data_chunk)
if errors:
raise ValueError(f"Encountered errors while inserting sub-batch: {errors}")
def create_and_load_table(
client: bigquery.Client | None,
project_id: str,
dataset_id: str,
table_name: str,
schema_def: Sequence[tuple[str, str, int | None]],
num_rows: int,
executor: concurrent.futures.Executor,
):
"""Creates a BigQuery table and loads data into it by consuming a data generator."""
if not client:
print(f"Simulating: Generated schema: {schema_def}")
return
# BQ client library streaming insert batch size (rows per API call)
# This is different from data_gen_batch_size which is for generating data.
# We can make BQ_LOAD_BATCH_SIZE smaller than data_gen_batch_size if needed.
BQ_LOAD_BATCH_SIZE = 500
# Actual BigQuery operations occur here because both project_id and dataset_id are provided
print(
f"Attempting BigQuery operations for table {table_name} in project '{project_id}', dataset '{dataset_id}'."
)
table_id = f"{project_id}.{dataset_id}.{table_name}"
bq_schema = []
for col_name, type_name, _ in schema_def:
bq_schema.append(bigquery.SchemaField(col_name, type_name))
table = bigquery.Table(table_id, schema=bq_schema)
print(f"(Re)creating table {table_id}...")
table = client.create_table(table, exists_ok=True)
print(f"Table {table_id} created successfully or already exists.")
# Query in case there's something in the streaming buffer already.
table_rows = next(
iter(client.query_and_wait(f"SELECT COUNT(*) FROM `{table_id}`"))
)[0]
print(f"Table {table_id} has {table_rows} rows.")
num_rows = max(0, num_rows - table_rows)
if num_rows <= 0:
print(f"No rows to load. Requested {num_rows} rows. Skipping.")
return
print(f"Starting to load {num_rows} rows into {table_id} in batches...")
previous_status_time = 0.0
generated_rows_total = 0
for completed_rows in executor.map(
worker_process_item,
generate_work_items(
table_id,
schema_def,
num_rows,
BQ_LOAD_BATCH_SIZE,
),
):
generated_rows_total += completed_rows
current_time = time.monotonic()
if current_time - previous_status_time > 5:
print(f"Wrote {generated_rows_total} out of {num_rows} rows.")
previous_status_time = current_time
worker_client: bigquery.Client | None = None
worker_rng: np.random.Generator | None = None
def worker_initializer(project_id: str | None):
global worker_client, worker_rng
# One client per process, since multiprocessing and client connections don't
# play nicely together.
if project_id is not None:
worker_client = bigquery.Client(project=project_id)
worker_rng = np.random.default_rng()
def worker_process_item(
work_item: tuple[str, Sequence[tuple[str, str, int | None]], int],
):
global worker_client, worker_rng
if worker_client is None or worker_rng is None:
raise ValueError("Worker not initialized.")
table_id, schema_def, num_rows = work_item
generate_and_load_batch(worker_client, table_id, schema_def, num_rows, worker_rng)
return num_rows
# --- Main Script Logic ---
def main():
"""Main function to create and populate BigQuery tables."""
parser = argparse.ArgumentParser(
description="Generate and load BigQuery benchmark tables."
)
parser.add_argument(
"-p",
"--project_id",
type=str,
default=None,
help="Google Cloud Project ID. If not provided, script runs in simulation mode.",
)
parser.add_argument(
"-d",
"--dataset_id",
type=str,
default=None,
help="BigQuery Dataset ID within the project. If not provided, script runs in simulation mode.",
)
args = parser.parse_args()
num_percentiles = len(TABLE_STATS["percentile"])
client = None
if args.project_id and args.dataset_id:
client = bigquery.Client(project=args.project_id)
dataset = bigquery.Dataset(f"{args.project_id}.{args.dataset_id}")
client.create_dataset(dataset, exists_ok=True)
with concurrent.futures.ProcessPoolExecutor(
initializer=worker_initializer, initargs=(args.project_id,)
) as executor:
for i in range(num_percentiles):
percentile = TABLE_STATS["percentile"][i]
avg_row_bytes_raw = TABLE_STATS["avg_row_bytes"][i]
table_bytes_raw = TABLE_STATS["materialized_or_scanned_bytes"][i]
target_table_bytes = max(1, int(math.ceil(table_bytes_raw)))
target_row_bytes = max(1, int(math.ceil(avg_row_bytes_raw)))
num_rows = max(1, int(math.ceil(target_table_bytes / target_row_bytes)))
table_name = f"percentile_{percentile:02d}"
print(f"\n--- Processing Table: {table_name} ---")
print(f"Target average row bytes (rounded up): {target_row_bytes}")
print(f"Number of rows (rounded up): {num_rows}")
schema_definition = get_bq_schema(target_row_bytes)
print(f"Generated Schema: {schema_definition}")
create_and_load_table(
client,
args.project_id or "",
args.dataset_id or "",
table_name,
schema_definition,
num_rows,
executor,
)
if __name__ == "__main__":
main()