11 type OssSelectJsonSuite struct {
16 var _ = Suite(&OssSelectJsonSuite{})
18 func (s *OssSelectJsonSuite) SetUpSuite(c *C) {
19 client, err := New(endpoint, accessID, accessKey)
22 s.client.Config.LogLevel = Error // Debug
23 err = s.client.CreateBucket(bucketName)
25 bucket, err := s.client.Bucket(bucketName)
29 testLogger.Println("test select json started")
32 func(s *OssSelectJsonSuite) TearDownSuite(c *C){
36 lor, err := s.bucket.ListObjects(marker)
38 for _, object := range lor.Objects {
39 err = s.bucket.DeleteObject(object.Key)
42 marker = Marker(lor.NextMarker)
48 err := s.client.DeleteBucket(bucketName)
51 testLogger.Println("test select json completed")
54 func (s *OssSelectJsonSuite) SetUpTest(c *C) {
55 testLogger.Println("test func", c.TestName(), "start")
58 func (s *OssSelectJsonSuite) TearDownTest(c *C) {
59 testLogger.Println("test func", c.TestName(), "succeed")
62 func (s *OssSelectJsonSuite) TestCreateSelectJsonObjectMeta(c *C){
63 key := "sample_json_lines.json"
64 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json_lines.json")
66 jsonMeta := JsonMetaRequest{
67 InputSerialization: InputSerialization {
73 res,err := s.bucket.CreateSelectJsonObjectMeta(key, jsonMeta)
75 c.Assert(res.RowsCount, Equals, int64(100))
77 err = s.bucket.DeleteObject(key)
81 func (s *OssSelectJsonSuite) TestSelectJsonDocument(c *C){
82 key := "sample_json.json"
83 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json.json")
85 selReq := SelectRequest{}
86 selReq.Expression = "select * from ossobject.objects[*] where party = 'Democrat'"
87 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "DOCUMENT"
88 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
90 var responseHeader http.Header
91 body, err := s.bucket.SelectObject(key, selReq, GetResponseHeader(&responseHeader))
94 requestId := GetRequestId(responseHeader)
95 c.Assert(len(requestId) > 0, Equals, true)
97 p := make([]byte, 512)
98 n, err := body.Read(p)
100 c.Assert(n, Equals, 512)
101 p1 := make([]byte, 3)
102 _, err = body.Read(p1)
104 rets, err := ioutil.ReadAll(body)
106 str,err := readJsonDocument("../sample/sample_json.json")
108 c.Assert(string(p) + string(p1) + string(rets), Equals, str)
110 err = s.bucket.DeleteObject(key)
114 func (s *OssSelectJsonSuite) TestSelectJsonLines(c *C) {
115 key := "sample_json_lines.json"
116 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json_lines.json")
118 selReq := SelectRequest{}
119 selReq.Expression = "select * from ossobject where party = 'Democrat'"
120 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
121 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "LINES"
123 var responseHeader http.Header
124 body, err := s.bucket.SelectObject(key, selReq, GetResponseHeader(&responseHeader))
128 requestId := GetRequestId(responseHeader)
129 c.Assert(len(requestId) > 0, Equals, true)
131 rets, err := ioutil.ReadAll(body)
133 str,err := readJsonDocument("../sample/sample_json.json")
134 c.Assert(string(rets), Equals, str)
136 err = s.bucket.DeleteObject(key)
140 func (s *OssSelectJsonSuite) TestSelectJsonLinesIntoFile(c *C) {
141 key := "sample_json_lines.json"
142 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json_lines.json")
145 jsonMeta := JsonMetaRequest{
146 InputSerialization: InputSerialization {
152 res,err := s.bucket.CreateSelectJsonObjectMeta(key, jsonMeta)
154 c.Assert(res.RowsCount, Equals, int64(100))
156 selReq := SelectRequest{}
157 selReq.Expression = "select * from ossobject where party = 'Democrat'"
158 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
159 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "LINES"
161 var responseHeader http.Header
162 outfile := "sample_json_out.json"
163 err = s.bucket.SelectObjectIntoFile(key, outfile, selReq, GetResponseHeader(&responseHeader))
165 requestId := GetRequestId(responseHeader)
166 c.Assert(len(requestId) > 0, Equals, true)
168 _, err = os.Stat(outfile)
170 err = os.Remove(outfile)
173 err = s.bucket.DeleteObject(key)
177 func (s *OssSelectJsonSuite) TestSelectJsonDocumentIntoFile(c *C) {
178 key := "sample_json_lines.json"
179 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json.json")
182 selReq := SelectRequest{}
183 selReq.Expression = "select * from ossobject.objects[*] where party = 'Democrat'"
184 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
185 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "DOCUMENT"
187 var responseHeader http.Header
188 outfile := "sample_json_out.json"
189 err = s.bucket.SelectObjectIntoFile(key, outfile, selReq, GetResponseHeader(&responseHeader))
191 requestId := GetRequestId(responseHeader)
192 c.Assert(len(requestId) > 0, Equals, true)
194 _, err = os.Stat(outfile)
196 err = os.Remove(outfile)
199 err = s.bucket.DeleteObject(key)
203 func (s *OssSelectJsonSuite) TestSelectJsonLinesLike(c *C) {
204 key := "sample_json_lines.json"
205 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json_lines.json")
207 selReq := SelectRequest{}
208 selReq.Expression = "select person.firstname, person.lastname from ossobject where person.birthday like '1959%'"
209 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
210 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "LINES"
212 jsonMeta := JsonMetaRequest{
213 InputSerialization: InputSerialization {
219 res,err := s.bucket.CreateSelectJsonObjectMeta(key, jsonMeta)
221 c.Assert(res.RowsCount, Equals, int64(100))
223 var responseHeader http.Header
224 body, err := s.bucket.SelectObject(key, selReq, GetResponseHeader(&responseHeader))
228 requestId := GetRequestId(responseHeader)
229 c.Assert(len(requestId) > 0, Equals, true)
231 rets, err := ioutil.ReadAll(body)
233 str,err := readJsonLinesLike("../sample/sample_json.json")
234 c.Assert(string(rets), Equals, str)
236 err = s.bucket.DeleteObject(key)
240 func (s *OssSelectJsonSuite) TestSelectJsonLinesRange(c *C) {
241 key := "sample_json_lines.json"
242 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json_lines.json")
244 jsonMeta := JsonMetaRequest{
245 InputSerialization: InputSerialization {
251 res,err := s.bucket.CreateSelectJsonObjectMeta(key, jsonMeta)
253 c.Assert(res.RowsCount, Equals, int64(100))
255 selReq := SelectRequest{}
256 selReq.Expression = "select person.firstname as aaa as firstname, person.lastname, extra from ossobject'"
257 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
258 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "LINES"
259 selReq.InputSerializationSelect.JsonBodyInput.Range = "0-1"
261 var responseHeader http.Header
262 body, err := s.bucket.SelectObject(key, selReq, GetResponseHeader(&responseHeader))
266 requestId := GetRequestId(responseHeader)
267 c.Assert(len(requestId) > 0, Equals, true)
269 rets, err := ioutil.ReadAll(body)
271 str,err := readJsonLinesRange("../sample/sample_json.json", 0, 2)
272 c.Assert(string(rets), Equals, str)
274 err = s.bucket.DeleteObject(key)
278 func (s *OssSelectJsonSuite) TestSelectJsonDocumentIntAggregation(c *C) {
279 key := "sample_json.json"
280 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json.json")
283 selReq := SelectRequest{}
284 selReq.Expression = `
286 avg(cast(person.cspanid as int)), max(cast(person.cspanid as int)),
287 min(cast(person.cspanid as int))
291 person.cspanid = 1011723
293 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
294 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "Document"
296 body, err := s.bucket.SelectObject(key, selReq)
300 rets, err := ioutil.ReadAll(body)
302 c.Assert(string(rets), Equals, "{\"_1\":1011723,\"_2\":1011723,\"_3\":1011723},")
304 err = s.bucket.DeleteObject(key)
308 func (s *OssSelectJsonSuite) TestSelectJsonDocumentFloatAggregation(c *C) {
309 key := "sample_json.json"
310 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json.json")
313 selReq := SelectRequest{}
314 selReq.Expression = `
316 avg(cast(person.cspanid as double)), max(cast(person.cspanid as double)),
317 min(cast(person.cspanid as double))
321 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
322 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "Document"
324 body, err := s.bucket.SelectObject(key, selReq)
328 rets, err := ioutil.ReadAll(body)
330 testLogger.Println(string(rets))
331 // avg, max, min, err := readJsonFloatAggregation("../sample/sample_json.json")
332 // fmt.Println(string(rets), "\n", avg, max, min)
333 // retsArr := strings.Split(string(rets), ":")
334 // s1 := strconv.FormatFloat(avg, 'f', 6, 64) + ","
335 // s1 += strconv.FormatFloat(max, 'f', 6, 64) + ","
336 // s1 += strconv.FormatFloat(min, 'f', 6, 64) + ","
338 // l := len(retsArr[1])
339 // vv, err := strconv.ParseFloat(retsArr[1][:l-35], 64)
340 // c.Assert(err, IsNil)
341 // retS += strconv.FormatFloat(vv, 'f', 6, 64) + ","
342 // l = len(retsArr[2])
343 // vv, err = strconv.ParseFloat(retsArr[2][:l-6], 64)
344 // c.Assert(err, IsNil)
345 // retS += strconv.FormatFloat(vv, 'f', 6, 64) + ","
346 // l = len(retsArr[3])
347 // vv, err = strconv.ParseFloat(retsArr[3][:l-2], 64)
348 // c.Assert(err, IsNil)
349 // retS += strconv.FormatFloat(vv, 'f', 6, 64) + ","
350 // c.Assert(retS, Equals, s1)
352 err = s.bucket.DeleteObject(key)
356 func (s *OssSelectJsonSuite) TestSelectJsonDocumentConcat(c *C) {
357 key := "sample_json.json"
358 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json.json")
361 selReq := SelectRequest{}
362 selReq.Expression = `
368 (person.firstname || person.lastname) = 'JohnKennedy'
370 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
371 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "Document"
373 body, err := s.bucket.SelectObject(key, selReq)
377 rets, err := ioutil.ReadAll(body)
379 str, err := readJsonDocumentConcat("../sample/sample_json.json")
381 c.Assert(string(rets), Equals, str)
383 err = s.bucket.DeleteObject(key)
387 func (s *OssSelectJsonSuite) TestSelectJsonComplicateConcat(c *C) {
388 key := "sample_json.json"
389 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json_lines.json")
392 selReq := SelectRequest{}
393 selReq.Expression = `
395 person.firstname, person.lastname, congress_numbers
399 startdate > '2017-01-01' and
400 senator_rank = 'junior' or
404 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
405 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "LINES"
407 body, err := s.bucket.SelectObject(key, selReq)
411 rets, err := ioutil.ReadAll(body)
413 str, err := readJsonComplicateConcat("../sample/sample_json.json")
415 c.Assert(string(rets), Equals, str)
417 err = s.bucket.DeleteObject(key)
421 func (s *OssSelectJsonSuite) TestSelectJsonLineInvalidSql(c *C) {
422 key := "sample_json.json"
423 err := s.bucket.PutObjectFromFile(key, "../sample/sample_json_lines.json")
426 selReq := SelectRequest{}
427 selReq.OutputSerializationSelect.JsonBodyOutput.RecordDelimiter = ","
428 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "LINES"
430 selReq.Expression = `select * from ossobject where avg(cast(person.birthday as int)) > 2016`
431 _, err = s.bucket.SelectObject(key, selReq)
432 c.Assert(err, NotNil)
434 selReq.Expression = ``
435 _, err = s.bucket.SelectObject(key, selReq)
436 c.Assert(err, NotNil)
438 selReq.Expression = `select person.lastname || person.firstname from ossobject`
439 _, err = s.bucket.SelectObject(key, selReq)
440 c.Assert(err, NotNil)
442 selReq.Expression = `select * from ossobject group by person.firstname`
443 _, err = s.bucket.SelectObject(key, selReq)
444 c.Assert(err, NotNil)
446 selReq.Expression = `select * from ossobject order by _1`
447 _, err = s.bucket.SelectObject(key, selReq)
448 c.Assert(err, NotNil)
450 selReq.Expression = `select * from ossobject oss join s3object s3 on oss.CityName = s3.CityName`
451 _, err = s.bucket.SelectObject(key, selReq)
452 c.Assert(err, NotNil)
454 err = s.bucket.PutObjectFromFile(key, "../sample/sample_json.json")
456 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "DOCUMENT"
457 selReq.Expression = `select _1 from ossobject.objects[*]`
458 body, err := s.bucket.SelectObject(key, selReq)
462 err = s.bucket.DeleteObject(key)
466 func (s *OssSelectJsonSuite) TestSelectJsonParseNumAsString(c *C) {
467 key := "sample_json.json"
468 content := "{\"a\":123456789.123456789}"
469 err := s.bucket.PutObject(key, strings.NewReader(content))
472 selReq := SelectRequest{}
473 selReq.Expression = `select a from ossobject where cast(a as decimal) = 123456789.1234567890`
475 selReq.InputSerializationSelect.JsonBodyInput.ParseJSONNumberAsString = &bo
476 selReq.InputSerializationSelect.JsonBodyInput.JSONType = "DOCUMENT"
478 body, err := s.bucket.SelectObject(key, selReq)
482 rets, err := ioutil.ReadAll(body)
484 c.Assert(string(rets), Equals, "{\"a\":123456789.123456789}\n")
486 err = s.bucket.DeleteObject(key)