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:"datesTime"`
}
// DatesTime shows different date/time representation
type
DatesTime
struct
{
Day
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"
,
err
)
}
defer
client
.
Close
()
// Manually defining schema
schema
:=
bigquery
.
Schema
{
{
Name
:
"name"
,
Type
:
bigquery
.
StringFieldType
},
{
Name
:
"age"
,
Type
:
bigquery
.
IntegerFieldType
},
{
Name
:
"school"
,
Type
:
bigquery
.
BytesFieldType
},
{
Name
:
"location"
,
Type
:
bigquery
.
GeographyFieldType
},
{
Name
:
"measurements"
,
Type
:
bigquery
.
FloatFieldType
,
Repeated
:
true
},
{
Name
:
"datesTime"
,
Type
:
bigquery
.
RecordFieldType
,
Schema
:
bigquery
.
Schema
{
{
Name
:
"day"
,
Type
:
bigquery
.
DateFieldType
},
{
Name
:
"firstTime"
,
Type
:
bigquery
.
DateTimeFieldType
},
{
Name
:
"secondTime"
,
Type
:
bigquery
.
TimeFieldType
},
{
Name
:
"thirdTime"
,
Type
:
bigquery
.
TimestampFieldType
},
}},
}
// Infer schema from struct
// schema, err := bigquery.InferSchema(ComplexType{})
table
:=
client
.
Dataset
(
datasetID
).
Table
(
tableID
)
err
=
table
.
Create
(
ctx
,
& bigquery
.
TableMetadata
{
Schema
:
schema
,
})
if
err
!=
nil
{
return
fmt
.
Errorf
(
"table.Create: %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
err
!=
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 University"
),
Location
:
bigquery
.
NullGeography
{
GeographyVal
:
"POINT(1 2)"
,
Valid
:
true
},
Measurements
:
[]
float64
{
50.05
,
100.5
},
DatesTime
:
DatesTime
{
Day
:
day
,
FirstTime
:
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
.
newBuilder
(
tableId
,
tableDefinition
).
build
();
bigquery
.
create
(
tableInfo
);
// Inserting Sample data
Map<String
,
Object
>
datesTimeContent
=
new
HashMap
<> ();
datesTimeContent
.
put
(
"day"
,
"2019-1-12"
);
datesTimeContent
.
put
(
"firstTime"
,
"2019-02-17 11:24:00.000"
);
datesTimeContent
.
put
(
"secondTime"
,
"14:00:00"
);
datesTimeContent
.
put
(
"thirdTime"
,
"2020-04-27T18:07:25.356Z"
);
Map<String
,
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"
,
datesTimeContent
);
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
.
Entry<Long
,
List<BigQueryError>
>
entry
:
response
.
getInsertErrors
().
entrySet
())
{
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
(
' @google-cloud/bigquery
'
);
const
bigquery
=
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
:
' INTEGER
'
,
},
{
name
:
'school'
,
type
:
' BYTES
'
,
},
{
name
:
'metadata'
,
type
:
' JSON
'
,
},
{
name
:
'location'
,
type
:
' GEOGRAPHY
'
,
},
{
name
:
'measurements'
,
mode
:
' REPEATED
'
,
type
:
'FLOAT'
,
},
{
name
:
'datesTimes'
,
type
:
' RECORD
'
,
fields
:
[
{
name
:
'day'
,
type
:
'DATE'
,
},
{
name
:
'firstTime'
,
type
:
' DATETIME
'
,
},
{
name
:
'secondTime'
,
type
:
' TIME
'
,
},
{
name
:
'thirdTime'
,
type
:
'TIMESTAMP'
,
},
],
},
];
// For all options, see https://cloud.google.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 DATE value 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 absolute point in time,
// independent of any time zone or convention such as Daylight
// Savings Time with microsecond precision.
const
bqTimestamp
=
bigquery
.
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
:
bqTime
,
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`
);
}
What's next
To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser .
