[Feature]: Schema for unstructured databases like Google Sheets

[leotom2000]

Is there an existing issue for this?

• I have searched the existing issues

Summary

Need to have a schema implementation for unstructured databases. It can allow users to define the datatype of columns.
This can solve data inconsistent problem by narrowing the options for users to add data of different datatype on a same column.

Implementation:
(1) Provide a User Interface popup in the query page to preview / define / override the column datatype.
(2) Validate button can preview existing data and auto suggest column datatype.
(2.1) Validate function also alerts data inconsistencies that would have been added manually.
(3) Should prevent users from adding inconsistent data.

Why should this be worked on?

Data stores like google sheets cannot define datatype for a column, at the same time they accept any datatype data for a column. so working with different datatype on the same column gives error while fetching condition based queries.

Front conversations

[nidhi-nair]

Related #7794

[yan-hic]

I have a list of invoice "numbers" in GSheet. When using it as source for a table, and the first row is truly numeric, any non-numeric values get stripped at API-level.
I can see in my Fetch SheetRows RAW result that AS strips string values for invoice as it could not coerce to Number.

An invoice number is actually more an ID, and can be in any string-format e.g. ABC-123

Workaround:

1. create a dummy second (after headers) row with values of 1 or a
2. add a row offset of 1 to the Fetch API
   -> luckily AS autodetects before offsetting so it uses the 1 and a to guess the column datatype, respectively Number and Plain Text

[trialsin]

I've that problem too. Using the workaround for now!

[yan-hic]

Unfortunately the workaround of a dummy top row does not always work. I am using Connected Sheets in GSheets for a way to read BigQuery data (BQ -> Sheets -> A/S) and the extracted data cannot be changed, hence cannot insert a row.

Has this feature request got any traction ?