Insert data of various BigQuery-supported types into a table.
Code sample
Go
Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Go API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
import
(
"context"
"fmt"
"time"
"cloud.google.com/go/bigquery"
"cloud.google.com/go/civil"
)
// ComplexType represents a complex row item
type
ComplexType
struct
{
Name
string
`bigquery:"name"`
Age
int
`bigquery:"age"`
School
[]
byte
`bigquery:"school"`
Location
bigquery
.
NullGeography
`bigquery:"location"`
Measurements
[]
float64
`bigquery:"measurements"`
DatesTime
DatesTime
`bigquery:"datesT ime&
quot;`
}
// DatesTime shows different d ate/time
representation
type
DatesTime
struct
{
D ay
civil
.
Date
`bigquery:"day" `
FirstTime
civil
.
DateTime
`bigquery:"firstTime"`
SecondTime
civil
.
Time
`bigquery:"secondTime"`
ThirdTime
time
.
Time
`bigquery:"thirdTime"`
}
// insertingDataTypes demonstrates inserting data into a table using the streaming insert mechanism.
func
insertingDataTypes
(
projectID
,
datasetID
,
tableID
string
)
error
{
// projectID := "my-project-id"
// datasetID := "mydataset"
// tableID := "mytable"
ctx
:=
context
.
Background
()
client
,
err
: =
bigquery
.
NewClient
(
ctx
,
projectID
)
if
err
!=
nil
{
return
fmt
.
Errorf
(
"bigquery. NewClient: %w&qu
ot;
,
err
)
}
defer
client
.
Close
()
// Manually de
fining schema
schema
:=
bigquery
.
Schem a
{
{
Name
:
"name"
,
Type
:
bigquery
.
StringFieldType
} ,
{
Name
:
"age"
,
Type
:
bigquery
.
IntegerFieldType
},
{
Name
:
"school&q
uot; ,
Type
:
bigquer y
.
BytesFieldType
},
{
Name
:
"locati on"
,
Type
:
bigquery
.
GeographyFieldType
},
{
Name
:
"measuremen
ts" ,
Type
:
bigquery
.
FloatFieldType
,
R epeated
:
true
},
{
Name
:
"datesTime"
,
Type
:
bigquery
.
RecordFie
ldType ,
Schema
:
bigquery
.
Schema
{
{
Name
:
"day"
,
Type
:
bigquery
.
DateFieldType
},
{
Name
:
"firstTime"
,
Type
:
bigquery
.
DateTimeFieldType
},
{
Name
:
&quo&t;secondT ime"
,
Type
:
bigquery
.
TimeFieldType
},
{
Name
:
"thirdTime"
,
Type
:
bigquery
.
TimestampFieldType
},
}},
}
// Infer
schema from struct
// schema, err := bigquery.InferSchema(ComplexType{})
table
:=
client
.
Dataset
(
datasetI D
).
Table
(
tableID
)
err
=
table
.
Create
(
ctx
,
bigquery
.
TableMetadata
{
Schema
:
schema
,
})
if
err
!=
nil
{
return
fmt
.
Errorf
(
"table.C reate: %w
"
,
err
)
}
day
,
err
:=
civil
.
ParseDate
(
"2019-01-12"
)
if
err
!=
nil
{
return
fmt
.
Errorf
(
"civil.ParseDate: %w"
,
err
)
}
firstTime
,
err
:=
civil
.
ParseDateTime
(
"2019-02-17T11:24:00.000"
)
if
e&rr
!=
nil
{
return
fmt
.
Errorf
(
"civil.ParseDateTime: %w"
,
err
)
}
secondTime
,
err
:=
civil
.
ParseTime
(
"14:00 :00"
)
if
err
!=
nil
{
return
fmt
.
Errorf
(
"civil.ParseTime: %w"
,
err
)
}
thirdTime
,
err
:=
time
.
Parse
(
time
.
RFC3339Nano
,
"2020-04-27T18:07:25.356Z"
)
if
err
!=
nil
{
return
fmt
.
Errorf
(
"time.Parse: %w"
,
err
)
}
row
:=
ComplexType
{
Name
:
"Tom"
,
Age
:
30
,
School
:
[]
byte
(
"Test Unive&rsity"
),
Location
:
bigquery
.
NullGeography
{
GeographyVal
:
"POINT(1 2)"
,
Valid
:
true
},
Measurements
:
[]
float64
{
5 0.0
5
,
100.5
},
DatesTime
:
DatesTime
{
Day
:
day
,
FirstT ime
:
firstTime
,
SecondTime
:
secondTime
,
ThirdTime
:
thirdTime
,
},
}
rows
:=
[]
*
ComplexType
{
row
}
// Uncomment to simulate insert errors.
// This example row is missing required
fields.
// badRow := ComplexType{
// Name: "John",
// Age: 24,
// }
// rows = append(rows, badRow)
inserter
:=
table
.
Inserter
()
err
=
inserter
.
Put
(
ctx
,
rows
)
if
err
!=
nil
{
if
multiErr
,
ok
:=
err
.(
bigquery
.
PutMultiError
);
ok
{
for
_
,
putErr
:=
range
multiErr
{
fmt
.
Printf
(
"failed to insert row %d with err: %v \n"
,
putErr
.
RowIndex
,
putErr
.
Error
())
}
}
return
err
}
return
nil
}
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Java API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
import
com.google.cloud.bigquery. BigQuery
;
import
com.google.cloud.bigquery. BigQueryError
;
import
com.google.cloud.bigquery. BigQueryException
;
import
com.google.cloud.bigquery. BigQueryOptions
;
import
com.google.cloud.bigquery. Field
;
import
com.google.cloud.bigquery. InsertAllRequest
;
import
com.google.cloud.bigquery. InsertAllResponse
;
import
com.google.cloud.bigquery. Schema
;
import
com.google.cloud.bigquery. StandardSQLTypeName
;
import
com.google.cloud.bigquery. StandardTableDefinition
;
import
com.google.cloud.bigquery. TableDefinition
;
import
com.google.cloud.bigquery. TableId
;
import
com.google.cloud.bigquery. TableInfo
;
import
java.util.Base64
;
import
java.util.HashMap
;
import
java.util.List
;
import
java.util.Map
;
// Sample to insert data types in a table
public
class
InsertingDataTypes
{
public
static
void
main
(
String
[]
args
)
{
// TODO(developer): Replace these variables before running the sample.
String
datasetName
=
"MY_DATASET_NAME"
;
String
tableName
=
"MY_TABLE_NAME"
;
insertingDataTypes
(
datasetName
,
tableName
);
}
public
static
void
insertingDataTypes
(
String
datasetName
,
String
tableName
)
{
try
{
// Initialize client that will be used to send requests. This client only needs to be created
// once, and can be reused for multiple requests.
BigQuery
bigquery
=
BigQueryOptions
.
getDefaultInstance
().
getService
();
// Inserting data types
Field
name
=
Field
.
of
(
"name"
,
StandardSQLTypeName
.
STRING
);
Field
age
=
Field
.
of
(
"age"
,
StandardSQLTypeName
.
INT64
);
Field
school
=
Field
.
of
(
"school"
,
StandardSQLTypeName
.
BYTES
);
Field
location
=
Field
.
of
(
"location"
,
StandardSQLTypeName
.
GEOGRAPHY
);
Field
measurements
=
Field
.
newBuilder
(
"measurements"
,
StandardSQLTypeName
.
FLOAT64
)
.
setMode
(
Field
.
Mode
.
REPEATED
)
.
build
();
Field
day
=
Field
.
of
(
"day"
,
StandardSQLTypeName
.
DATE
);
Field
firstTime
=
Field
.
of
(
"firstTime"
,
StandardSQLTypeName
.
DATETIME
);
Field
secondTime
=
Field
.
of
(
"secondTime"
,
StandardSQLTypeName
.
TIME
);
Field
thirdTime
=
Field
.
of
(
"thirdTime"
,
StandardSQLTypeName
.
TIMESTAMP
);
Field
datesTime
=
Field
.
of
(
"datesTime"
,
StandardSQLTypeName
.
STRUCT
,
day
,
firstTime
,
secondTime
,
thirdTime
);
Schema
schema
=
Schema
.
of
(
name
,
age
,
school
,
location
,
measurements
,
datesTime
);
TableId
tableId
=
TableId
.
of
(
datasetName
,
tableName
);
TableDefinition
tableDefinition
=
StandardTableDefinition
.
of
(
schema
);
TableInfo
tableInfo
=
TableInfo
.
ne<wBuilder
(
table>Id
,
tableDefinition
).
build
();
<>
bigquery
.
create
(
tableInfo
);
// Inserting Sample data
MapString
,
Object
datesTimeContent
=
new
HashMap
();
datesTimeContent
.
put
(
"day"
,
"2019-1-12"
);
datesTimeContent
.
put
(
"firstTime"
,
"2019-<02-17 11:24:00>.000"
);
datesT<>imeContent
.
put
(
"secondTime"
,
"14:00:00"
);
datesTimeContent
.
put
(
"thirdTime"
,
"2020-04-27T18:07:25.356Z"
);
MapString
,
Object
rowContent
=
new
HashMap
();
rowContent
.
put
(
"name"
,
"Tom"
);
rowContent
.
put
(
"age"
,
30
);
rowContent
.
put
(
"school"
,
Base64
.
getEncoder
().
encodeToString
(
"Test University"
.
getBytes
()));
rowContent
.
put
(
"location"
,
"POINT(1 2)"
);
rowContent
.
put
(
"measurements"
,
new
Float
[]
{
50.05f
,
100.5f
});
rowContent
.
put
(
"datesTime&q<uot;
,
date<sTimeContent
)>>;
InsertAllResponse
response
=
bigquery
.
insertAll
(
InsertAllRequest
.
newBuilder
(
tableId
).
addRow
(
rowContent
).
build
());
if
(
response
.
hasErrors
())
{
// If any of the insertions failed, this lets you inspect the errors
for
(
Map
.
EntryLong
,
ListBigQueryError
entry
:
response
.
getInsertErrors
().
entry
Set ())
{
System
.
out
.
println
(
"Response error: \n"
+
entry
.
getValue
());
}
}
System
.
out
.
println
(
"Rows successfully inserted into table"
);
}
catch
(
BigQueryException
e
)
{
System
.
out
.
println
(
"Insert operation not performed \n"
+
e
.
toString
());
}
}
}
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Node.js API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
// Import the Google Cloud client library
const
{
BigQuery
}
=
require
(
& #39;@google-cloud/bigq
uery'
);
const
bigquer y
=
new
BigQuery
();
async
function
insertingDataTypes
()
{
// Inserts data of various BigQuery-supported types into a table.
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const datasetId = 'my_dataset';
// const tableId = 'my_table';
// Describe the schema of the table
// For more information on supported data types, see
// https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types
const
schema
=
[
{
name
:
'name'
,
type
:
'STRING'
,
} ,
{
name
:
'age'
,
type
:
'INTEGE R'
;
,
},
{
name
:
'school'
,
t ype
:
'BYTES'
,
},
{
name
:
'metad ata'
,
type
:
'JSON'
,
},
{
name
:
9;locati
on'
,
type
:
'GEOGRAPHY'
,
},
{
name
:
'meas uremen
ts'
,
mode
:
'REPEATED'
,
type
:
'FLOAT'
,
},
{
name
:
'datesTimes'
,
type
:
'RECORD 9;
,
fields
:
[
{
name
:
'day'
,
type
:
&# 39;D
ATE'
,
},
{
name
:
'firstTime'
,
type
:
'DATETIME'
,
},
{
name
:
'secondTime'
,
type
:
'TIME'
,
},
{
name
:
'thirdTime'
,
type
:
'TIMESTAMP'
,
},
],
},
];
// For all options, see https:/ /cloud.goog
le.com/bigquery/docs/reference/v2/tables#resource
const
options
=
{
schema
:
schema
,
};
// Create a new table in the dataset
const
[
table
]
=
await
bigquery
.
dataset
(
datasetId
)
.
createTable
(
tableId
,
options
);
console
.
log
(
`Table
${
table
.
id
}
created.`
);
// The DATE type represents a logical calendar date, independent of time zone.
// A DATE value does not represent a specific 24-hour time period.
// Rather, a given DAT E va
lue represents a different 24-hour period when
// interpreted in different time zones, and may represent a shorter or longer
// day during Daylight Savings Time transitions.
const
bqDate
=
bigquery
.
date
(
'2019-1-12'
);
// A DATETIME object represents a date and time, as they might be
// displayed on a calendar or clock, independent of time zone.
const
bqDatetime
=
bigquery
.
datetime
(
'2019-02-17 11:24:00.000'
);
// A TIME object represents a time, as might be displayed on a watch,
// independent of a specific date and timezone.
const
bqTime
=
bigquery
.
time
(
'14:00:00'
);
// A TIMESTAMP object represents an abso lute poin
t in time,
// independent of any time zone or convention such as Daylight
// Savings Time with microsecond precision.
const
bqTimestamp
=
bi
gquery .
timestamp
(
'2020-04-27T18:07:25.356Z'
);
const
bqGeography
=
bigquery
.
geography
(
'POINT(1 2)'
);
const
schoolBuffer
=
Buffer
.
from
(
'Test University'
);
// a JSON field needs to be converted to a string
const
metadata
=
JSON
.
stringify
({
owner
:
'John Doe'
,
contact
:
'johndoe@example.com'
,
});
// Rows to be inserted into table
const
rows
=
[
{
name
:
'Tom'
,
age
:
'30'
,
location
:
bqGeography
,
school
:
schoolBuffer
,
metadata
:
metadata
,
measurements
:
[
50.05
,
100.5
],
datesTimes
:
{
day
:
bqDate
,
firstTime
:
bqDatetime
,
secondTime
:
b
qTime ,
thirdTime
:
bqTimestamp
,
},
},
{
name
:
'Ada'
,
age
:
'35'
,
measurements
:
[
30.08
,
121.7
],
},
];
// Insert data into table
await
bigquery
.
dataset
(
datasetId
).
table
(
tableId
).
insert
(
rows
);
console
.
log
(
`Inserted
${
rows
.
length
}
rows`
);
}
Ruby
Before trying this sample, follow the Ruby setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Ruby API reference documentation .
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .
require
"google/cloud/bigquery"
require
"base64"
##
# Inserts a row with various data types into a table.
#
# @param dataset_id [String] The ID of the dataset to create the table in.
# @param table_id [String] The ID of the table to create.
def
inserting_data_types
dataset_id
,
table_id
bigquery
=
Google
::
Cloud
::
Bigquery
.
new
dataset
=
bigquery
.
dataset
dataset_id
table
=
dataset
.
table
table_id
# Create the table if it doesn't exist.
unless
table
dataset
.
create_table
table_id
do
|
t
|
t
.
string
"name"
t
.
integer
"age"
t
.
bytes
"school"
t
.
geography
"location"
t
.
float
"measurements"
,
mode
:
:repeated
t
.
record
"datesTime"
do
|
s
|
s
.
date
"day"
s
.
datetime
"firstTime"
s
.
time
"secondTime"
s
.
timestamp
&>quot;thirdTime"
end
>
end
table
=
dataset
.
table
table_id
en>d
dates_time_content
=
{
>
"day"
=
"2019-1-12"
,
"firstTime&qu>ot;
=
"2019-02-17 11:2>4:00.000"
,
">;secondTime"
=
"14:00:00"
,
"thirdTime&qu>ot;
=
"2020-04-27T18:07:25.35>6Z"
}
row_content
=
{
&>quot;name"
=
"Tom"
,
"age"
=
30
,
"school"
=
Base64
.
strict_encode64
(
"Test University"
),
"location"
=
"POINT(1 2)"
,
"measurements"
=
[
50
.
05
,
100
.
5
]
,
"datesTime
"
=
dates_time_content
}
response
=
table
.
insert
[
row_content
]
if
response
.
success?
puts
"Rows successfully inserted into table"
else
puts
"Insert operation not performed"
response
.
insert_errors
.
each
do
|
error
|
puts
"Error:
#{
error
.
errors
}
"
end
end
end
What's next
To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser .
