This sample demonstrates how to use a protocol buffer to write data into a BigQuery table.

## Code sample

### Python


Before trying this sample, follow the Python setup instructions in the
[BigQuery quickstart using
client libraries](https://docs.cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries).


For more information, see the
[BigQuery Python API
reference documentation](https://docs.cloud.google.com/python/docs/reference/bigquery/latest).


To authenticate to BigQuery, set up Application Default Credentials.
For more information, see

[Set up authentication for client libraries](https://docs.cloud.google.com/bigquery/docs/authentication#client-libs).

    """
    This code sample demonstrates using the low-level generated client for Python.
    """

    import datetime
    import decimal

    from google.protobuf import descriptor_pb2

    from google.cloud import https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest
    from google.cloud.bigquery_storage_v1 import https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html, https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1beta2.writer.html

    # If you make updates to the sample_data.proto protocol buffers definition,
    # run:
    #
    #   protoc --python_out=. sample_data.proto
    #
    # from the samples/snippets directory to generate the sample_data_pb2 module.
    from . import sample_data_pb2


    def append_rows_proto2(project_id: str, dataset_id: str, table_id: str):
        """Create a write stream, write some sample data, and commit the stream."""
        write_client = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest.BigQueryWriteClient()
        parent = write_client.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.services.big_query_write.BigQueryWriteClient.html#google_cloud_bigquery_storage_v1_services_big_query_write_BigQueryWriteClient_table_path(project_id, dataset_id, table_id)
        write_stream = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.WriteStream.html()

        # When creating the stream, choose the type. Use the PENDING type to wait
        # until the stream is committed before it is visible. See:
        # https://cloud.google.com/bigquery/docs/reference/storage/rpc/google.cloud.bigquery.storage.v1#google.cloud.bigquery.storage.v1.WriteStream.Type
        write_stream.type_ = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.WriteStream.html.Type.PENDING
        write_stream = write_client.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.services.big_query_write.BigQueryWriteClient.html#google_cloud_bigquery_storage_v1_services_big_query_write_BigQueryWriteClient_create_write_stream(
            parent=parent, write_stream=write_stream
        )
        stream_name = write_stream.name

        # Create a template with fields needed for the first request.
        request_template = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.html()

        # The initial request must contain the stream name.
        request_template.write_stream = stream_name

        # So that BigQuery knows how to parse the serialized_rows, generate a
        # protocol buffer representation of your message descriptor.
        proto_schema = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.ProtoSchema.html()
        proto_descriptor = descriptor_pb2.DescriptorProto()
        sample_data_pb2.SampleData.DESCRIPTOR.CopyToProto(proto_descriptor)
        proto_schema.proto_descriptor = proto_descriptor
        proto_data = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.ProtoData.html()
        proto_data.writer_schema = proto_schema
        request_template.proto_rows = proto_data

        # Some stream types support an unbounded number of requests. Construct an
        # AppendRowsStream to send an arbitrary number of requests to a stream.
        append_rows_stream = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1beta2.writer.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1beta2.writer.AppendRowsStream.html(write_client, request_template)

        # Create a batch of row data by appending proto2 serialized bytes to the
        # serialized_rows repeated field.
        proto_rows = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.ProtoRows.html()

        row = sample_data_pb2.SampleData()
        row.row_num = 1
        row.bool_col = True
        row.bytes_col = b"Hello, World!"
        row.float64_col = float("+inf")
        row.int64_col = 123
        row.string_col = "Howdy!"
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 2
        row.bool_col = False
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 3
        row.bytes_col = b"See you later!"
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 4
        row.float64_col = 1000000.125
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 5
        row.int64_col = 67000
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 6
        row.string_col = "Auf Wiedersehen!"
        proto_rows.serialized_rows.append(row.SerializeToString())

        # Set an offset to allow resuming this stream if the connection breaks.
        # Keep track of which requests the server has acknowledged and resume the
        # stream at the first non-acknowledged message. If the server has already
        # processed a message with that offset, it will return an ALREADY_EXISTS
        # error, which can be safely ignored.
        #
        # The first request must always have an offset of 0.
        request = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.html()
        request.offset = 0
        proto_data = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.ProtoData.html()
        proto_data.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.reader.ReadRowsStream.html#google_cloud_bigquery_storage_v1_reader_ReadRowsStream_rows = proto_rows
        request.proto_rows = proto_data

        response_future_1 = append_rows_stream.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1beta2.writer.AppendRowsStream.html#google_cloud_bigquery_storage_v1beta2_writer_AppendRowsStream_send(request)

        # Create a batch of rows containing scalar values that don't directly
        # correspond to a protocol buffers scalar type. See the documentation for
        # the expected data formats:
        # https://cloud.google.com/bigquery/docs/write-api#data_type_conversions
        proto_rows = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.ProtoRows.html()

        row = sample_data_pb2.SampleData()
        row.row_num = 7
        date_value = datetime.date(2021, 8, 12)
        epoch_value = datetime.date(1970, 1, 1)
        delta = date_value - epoch_value
        row.date_col = delta.days
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 8
        datetime_value = datetime.datetime(2021, 8, 12, 9, 46, 23, 987456)
        row.datetime_col = datetime_value.strftime("%Y-%m-%d %H:%M:%S.%f")
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 9
        row.geography_col = "POINT(-122.347222 47.651111)"
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 10
        numeric_value = decimal.Decimal("1.23456789101112e+6")
        row.numeric_col = str(numeric_value)
        bignumeric_value = decimal.Decimal("-1.234567891011121314151617181920e+16")
        row.bignumeric_col = str(bignumeric_value)
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 11
        time_value = datetime.time(11, 7, 48, 123456)
        row.time_col = time_value.strftime("%H:%M:%S.%f")
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 12
        timestamp_value = datetime.datetime(
            2021, 8, 12, 16, 11, 22, 987654, tzinfo=datetime.timezone.utc
        )
        epoch_value = datetime.datetime(1970, 1, 1, tzinfo=datetime.timezone.utc)
        delta = timestamp_value - epoch_value
        row.timestamp_col = int(delta.total_seconds()) * 1000000 + int(delta.microseconds)
        proto_rows.serialized_rows.append(row.SerializeToString())

        # Since this is the second request, you only need to include the row data.
        # The name of the stream and protocol buffers DESCRIPTOR is only needed in
        # the first request.
        request = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.html()
        proto_data = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.ProtoData.html()
        proto_data.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.reader.ReadRowsStream.html#google_cloud_bigquery_storage_v1_reader_ReadRowsStream_rows = proto_rows
        request.proto_rows = proto_data

        # Offset must equal the number of rows that were previously sent.
        request.offset = 6

        response_future_2 = append_rows_stream.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1beta2.writer.AppendRowsStream.html#google_cloud_bigquery_storage_v1beta2_writer_AppendRowsStream_send(request)

        # Create a batch of rows with STRUCT and ARRAY BigQuery data types. In
        # protocol buffers, these correspond to nested messages and repeated
        # fields, respectively.
        proto_rows = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.ProtoRows.html()

        row = sample_data_pb2.SampleData()
        row.row_num = 13
        row.int64_list.append(1)
        row.int64_list.append(2)
        row.int64_list.append(3)
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 14
        row.struct_col.sub_int_col = 7
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 15
        sub_message = sample_data_pb2.SampleData.SampleStruct()
        sub_message.sub_int_col = -1
        row.struct_list.append(sub_message)
        sub_message = sample_data_pb2.SampleData.SampleStruct()
        sub_message.sub_int_col = -2
        row.struct_list.append(sub_message)
        sub_message = sample_data_pb2.SampleData.SampleStruct()
        sub_message.sub_int_col = -3
        row.struct_list.append(sub_message)
        proto_rows.serialized_rows.append(row.SerializeToString())

        row = sample_data_pb2.SampleData()
        row.row_num = 16
        date_value = datetime.date(2021, 8, 8)
        epoch_value = datetime.date(1970, 1, 1)
        delta = date_value - epoch_value
        row.range_date.start = delta.days
        proto_rows.serialized_rows.append(row.SerializeToString())

        request = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.html()
        request.offset = 12
        proto_data = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.AppendRowsRequest.ProtoData.html()
        proto_data.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.reader.ReadRowsStream.html#google_cloud_bigquery_storage_v1_reader_ReadRowsStream_rows = proto_rows
        request.proto_rows = proto_data

        # For each request sent, a message is expected in the responses iterable.
        # This sample sends 3 requests, therefore expect exactly 3 responses.
        response_future_3 = append_rows_stream.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1beta2.writer.AppendRowsStream.html#google_cloud_bigquery_storage_v1beta2_writer_AppendRowsStream_send(request)

        # All three requests are in-flight, wait for them to finish being processed
        # before finalizing the stream.
        print(response_future_1.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1beta2.writer.AppendRowsFuture.html#google_cloud_bigquery_storage_v1beta2_writer_AppendRowsFuture_result())
        print(response_future_2.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1beta2.writer.AppendRowsFuture.html#google_cloud_bigquery_storage_v1beta2_writer_AppendRowsFuture_result())
        print(response_future_3.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1beta2.writer.AppendRowsFuture.html#google_cloud_bigquery_storage_v1beta2_writer_AppendRowsFuture_result())

        # Shutdown background threads and close the streaming connection.
        append_rows_stream.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1beta2.writer.AppendRowsStream.html#google_cloud_bigquery_storage_v1beta2_writer_AppendRowsStream_close()

        # A PENDING type stream must be "finalized" before being committed. No new
        # records can be written to the stream after this method has been called.
        write_client.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.services.big_query_write.BigQueryWriteClient.html#google_cloud_bigquery_storage_v1_services_big_query_write_BigQueryWriteClient_finalize_write_stream(name=write_stream.name)

        # Commit the stream you created earlier.
        batch_commit_write_streams_request = https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.html.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.BatchCommitWriteStreamsRequest.html()
        batch_commit_write_streams_request.parent = parent
        batch_commit_write_streams_request.write_streams = [write_stream.name]
        write_client.https://docs.cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.services.big_query_write.BigQueryWriteClient.html#google_cloud_bigquery_storage_v1_services_big_query_write_BigQueryWriteClient_batch_commit_write_streams(batch_commit_write_streams_request)

        print(f"Writes to stream: '{write_stream.name}' have been committed.")

## What's next


To search and filter code samples for other Google Cloud products, see the
[Google Cloud sample browser](https://docs.cloud.google.com/docs/samples?product=bigquery).