-
Notifications
You must be signed in to change notification settings - Fork 55
/
insert.go
220 lines (183 loc) · 6.06 KB
/
insert.go
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
package bigquery
import (
"context"
"fmt"
"os"
"path/filepath"
"strings"
"time"
"github.com/scratchdata/scratchdata/pkg/util"
"cloud.google.com/go/bigquery"
"github.com/rs/zerolog/log"
"github.com/scratchdata/scratchdata/pkg/storage/blobstore/gcs"
)
func (s *BigQueryServer) jsonTypeToBQType(jsonType string) bigquery.FieldType {
switch jsonType {
case "int":
return bigquery.IntegerFieldType
case "bool":
return bigquery.BooleanFieldType
case "float":
return bigquery.FloatFieldType
case "string":
return bigquery.StringFieldType
default:
return bigquery.StringFieldType
}
}
func (s *BigQueryServer) CreateEmptyTable(name string) error {
ctx := context.Background()
res := strings.Split(name, ".")
if len(res) != 2 {
log.Error().Str("table", name).Msg("CreateEmptyTable: table name should be in the format dataset.table")
return fmt.Errorf("table name should be in the format dataset.table")
}
// first create the dataset if missing
// using SDK, raw sql requires lot of other default params to be entered.
meta := &bigquery.DatasetMetadata{
Location: s.Location,
}
if err := s.conn.Dataset(res[0]).Create(ctx, meta); err != nil && !strings.Contains(err.Error(), "Error 409: Already Exists") {
log.Error().Err(err).Str("dataset", res[0]).Msg("CreateEmptyTable: failed to create Dataset")
return err
}
// does support BIGINT in raw SQL, this is alias for INT64 in bigquery
// create table requires both dataset and table name
query := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (__row_id BIGINT)", name)
_, err := s.conn.Query(query).Read(ctx)
if err != nil {
log.Error().Err(err).Str("query", query).Msg("CreateEmptyTable: failed to create Table")
return err
}
return nil
}
func (s *BigQueryServer) createColumns(table string, jsonTypes map[string]string) error {
ctx := context.Background()
for colName, jsonType := range jsonTypes {
colType := s.jsonTypeToBQType(jsonType)
query := fmt.Sprintf("ALTER TABLE `%s` ADD COLUMN IF NOT EXISTS `%s` %s", table, colName, colType)
_, err := s.conn.Query(query).Read(ctx)
if err != nil {
log.Error().Err(err).Str("query", query).Msg("createColumns: cannot run query")
return err
}
time.Sleep(2 * time.Second)
}
return nil
}
func (s *BigQueryServer) CreateColumns(table string, fileName string) error {
input, err := os.Open(fileName)
if err != nil {
log.Error().Err(err).Str("filename", fileName).Msg("CreateColumns: Unable to open file")
return err
}
// Infer JSON types for the input
jsonTypes, err := util.GetJSONTypes(input)
if err != nil {
log.Error().Err(err).Str("filename", fileName).Msg("CreateColumns: Unable to infer JSON types")
return err
}
err = s.createColumns(table, jsonTypes)
if err != nil {
log.Error().Err(err).Str("table", table).Msg("CreateColumns: Failed to create columns")
return err
}
err = input.Close()
if err != nil {
log.Error().Err(err).Str("filename", fileName).Msg("Unable to close file")
}
return nil
}
func (s *BigQueryServer) UploadAndStream(table string, filePath string) error {
client, err := gcs.NewStorage(map[string]any{
"bucket": s.GCSBucketName,
"credentials_json": s.CredentialsJsonString,
})
if err != nil {
log.Error().Err(err).Msg("Failed to create GCS client")
return err
}
gcsFilePath := ""
if s.GCSFilePrefix != "" {
gcsFilePath = s.GCSFilePrefix + "/"
}
gcsFilePath += table + "/" + filepath.Base(filePath)
log.Info().Msg("Uploading file to GCS ")
err = s.uploadFileToGCS(filePath, gcsFilePath, client)
if err != nil {
log.Error().Err(err).Str("file", filePath).Str("gcs_file", gcsFilePath).Msg("Failed to upload file to GCS")
return err
}
log.Info().Str("gcs_file", gcsFilePath).Msg("Uploaded file to GCS")
input, err := os.Open(filePath)
if err != nil {
log.Error().Err(err).Str("filename", filePath).Msg("Upload And Stream: Unable to open file")
return err
}
// Infer JSON types for the input
jsonTypes, err := util.GetJSONTypes(input)
if err != nil {
log.Error().Err(err).Str("filename", filePath).Msg("Upload And Stream: Unable to infer JSON types")
return err
}
log.Info().Msg("Streaming data to BigQuery")
err = s.streamDataToBigQuery(table, gcsFilePath, jsonTypes)
if err != nil {
log.Error().Err(err).Msg("Failed to stream data to BigQuery")
return err
}
if s.DeleteFromGCS {
log.Info().Msg("Deleting file from GCS")
err = client.Delete(gcsFilePath)
if err != nil {
log.Error().Err(err).Str("gcs_file", gcsFilePath).Msg("Failed to delete file from GCS")
} else {
log.Info().Msg("Deleted file from GCS")
}
}
return nil
}
func (s *BigQueryServer) uploadFileToGCS(filePath string, gcsFilePath string, client *gcs.Storage) error {
file, err := os.Open(filePath)
if err != nil {
log.Error().Err(err).Str("file", filePath).Msg("Failed to open file")
return err
}
err = client.Upload(gcsFilePath, file)
if err != nil {
log.Error().Err(err).Str("file", filePath).Str("gcs_file", gcsFilePath).Msg("Failed to upload file to GCS")
}
return nil
}
func (s *BigQueryServer) streamDataToBigQuery(table string, gcsFilePath string, jsonTypes map[string]string) error {
location := fmt.Sprintf("gs://%s/%s", s.GCSBucketName, gcsFilePath)
ctx := context.Background()
columns := "("
first := true
for colName, jsonType := range jsonTypes {
colType := s.jsonTypeToBQType(jsonType)
if !first {
columns += ", "
} else {
first = false
}
columns += fmt.Sprintf("`%s` %s", colName, colType)
}
columns += ")"
query := fmt.Sprintf("LOAD DATA INTO %s %s FROM FILES ( format = 'JSON', uris = ['%s'] )", table, columns, location)
_, err := s.conn.Query(query).Read(ctx)
if err != nil {
log.Error().Err(err).Str("query", query).Msg("StreamDataToBigQuery: failed to stream data to BigQuery")
return err
}
log.Info().Msg("Successfully loaded data into BigQuery")
return nil
}
func (s *BigQueryServer) InsertFromNDJsonFile(table string, filePath string) error {
err := s.UploadAndStream(table, filePath)
if err != nil {
log.Error().Err(err).Str("table", table).Str("file", filePath).Msg("Failed to upload and stream data to BigQuery")
return err
}
return nil
}