Physical DataSet creation over REST API

1

Hi,

I’m creating physical datasets from JSON files, over the catalog REST API.
When a JSON field is a TimeStamp it is always converted to VARCHAR in the physical dataset.

I have tried passing the following POST body to describe the JSON file but the result is always
a VARCHAR in the dataset.

{   
    "entityType": "dataset",
    "type": "PHYSICAL_DATASET",
    "format": { 
        "type": "JSON"
    },
    "path": [
        "creationDate.json"
    ],
    "fields": [
        {   
            "name": "CreationDate",
            "type": {
                "name": "TIMESTAMP"
            }
        }   
    ]       
}

The content of the JSON file looks like this

[{"CreationDate":"2022-03-02 03:25:22.850"}]

Is it possible to convert this string to a TIMESTAMP field in the resulting dataset?

Thanks
Wayne

2

Found a way to do this.
This Topic (On Date String conversion) Demonstrates how to format JSON so that it converts to a TIMESTAMP during Physical DataSet creation.

By providing dates in this JSON format,
Dremio will create a TIMESTAMP datatype

{"CreationDate":{"$date":"2022-03-25T10:15:30Z"}}

Thanks
Wayne

3

Hi @waynekoepcke Great find, glad you are no longer blocked on this