Presto: Support INSERT in Google Sheets connector

[theoretical-olive]

Currently, read operations work as expected.
Need ability to insert into a gsheet or table.

[parambole]

@theoretical-olive I will be working on this issue.

[parambole]

Flow with respect to Google sheets

Create table Statement:

Flow:

1. On running the create table command a new entry is created in the GSheet Metadata table.
2. New GSheet with column header is created

Insert Statement:

Flow:
First get the sheet id of table in the Insert statement. Insert the values in that sheet

Drop Statement

Flow:

In the metadata sheet replace the name of the table with something like table_<> or table_<>

Advantages of this method

1. When some one does a select or insert we check if the table exists in the metadata sheet. And since the name is changed its equivalent to the table does not exist
2. We can unarchive the table by changing the table name to the original name ( the admin of the sheet will have the access to do that )

@community I would like your opinions on the drop statement flow

[parambole]

@community Design wise would you suggest decoupling the metadata of tables from Google sheets ( metadata sheet ) to some other database like Mysql ( like hive ) Or do we support both? please let me know why you would choose one over the other.

[electrum]

Instead of changing the name, we could have a "deleted" column in the metadata table. Any tables marked as deleted would be ignored by the engine. Otherwise this flow looks good.

The reason this connector exists is to make it easy to import/export data. Having the metadata in a sheet allows end users to easily manage the table to sheet mapping. Storing the metadata externally doesn't seem particularly useful and adds additional complexity to the connector.

[parambole]

@electrum Thank you for replying. Yes, I considered that option too. I will change the flow for Drop table statement based on your previous comment.