How to upload csv to QuestDb with parsing column as timestamp?

Question

I want to import csv file into QuestDb table with designated timestamp column. I have to specify that one of the column is not a String but a Timestamp which I as

import requests
schema = '[{"name":"date", "type": "Timestamp", "pattern":"yyyy-MM-dd"}]'

with open('..\HavCases.csv', 'rb') as f:
    r = requests.post(r'http://localhost:9000/imp?name=table3&overwrite=true&timestamp=date',
        files={'schema': schema,'data': f})
    print(r.text)

but date column keep coming back as STRING and response is

not a timestamp 'date'

when I remove &timestamp from URL I see column parsed as STRING, not Timestamp

+-----------------------------------------------------------------------------------------------------------------+
|      Location:  |                                            table3  |        Pattern  | Locale  |      Errors  |
|   Partition by  |                                              NONE  |                 |         |              |
|      Timestamp  |                                              NONE  |                 |         |              |
+-----------------------------------------------------------------------------------------------------------------+
|   Rows handled  |                                               780  |                 |         |              |
|  Rows imported  |                                               780  |                 |         |              |
+-----------------------------------------------------------------------------------------------------------------+
...
|              9  |                                              date  |                   STRING  |           0  |

allnau · Accepted Answer · 2021-01-22 09:41:57Z

4

I found an aswer, it's not a trivial had to look into QuestDb source so posting it here for others

Apparently when QuestDb parses import request it expects 'schema' file to be first, before csv 'data'. When Python requests posts the data from files Dictionary it seems to post it in alphabetical order of the keys, so that files={'schema': schema,'data': f} and files={'data': f, 'schema': schema} are equivalent, data sent first and then schema.

To work around that I had to supply files parameter as list of tuples, not a dictionary:

with open(r'..\HavCases.csv', 'rb') as f:
    r = requests.post(r'http://localhost:9000/imp?name=table3&overwrite=true&timestamp=date', 
    files=[('schema', schema), ('data', f)])
    print(r.text)

answered Jan 22, 2021 at 9:41

allnau

3812 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to upload csv to QuestDb with parsing column as timestamp?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related