🐛 Airtable source: remove base & tables from spec.json, and use the API to list available bases & tables, using bases as the namespaces for streams

[sherifnada]

Tell us about the problem you're trying to solve

Airtable bases are equivalent to database schemas. We should use them as such.

Describe the solution you’d like

1. Remove the base & tables parameters from spec.json (we'll need to figure out how to handle backwards compatibility here, this is a tricky one)
2. Instead, list the bases inside the connector (when running discover) by calling the endpoint https://api.airtable.com/v0/meta/bases (see the docs) and set them as the namespace property of each available table (stream).
3. Then list all tables in a base , those are the streams and the base is the namespace
4. Return all this in discovery output

[bazarnov]

@misteryeo @sherifnada
In order to resolve this, we would need to obtain the Client-Secret in addition to API_KEY, we already have.
More info by the following link: https://airtable.com/shrWl6yu8cI8C5Dh3
Basically, we should provide additional header value X-Airtable-Client-Secret: <client_secret> to call Metadata API.
Read this link: https://airtable.com/api/meta Authorization section.

This brings us to the next:

• Airtable doesn't support OAuth2.0 flow, the only option is to use the API_KEY obtained by each customer on their own.

If we simplify the process like described in the issue the Customers will have to get the same approvals as we do by the link above. This is very inconvenient in terms of "start-and-go" with Airbyte. As an option we can force the Customers to proceed though the obtaining access to Metadata API before the connector setup as the required step, however I personally find this the long way. WDYT?

[sherifnada]

@bazarnov agreed with your reasoning for the short term. Can you leave this issue open? I think we may be able to get an exception from air table and integrate using oauth.

Cc @misteryeo

[patygold]

Hey! Do we have an estimation on this issue already?

I think this will actually solve one of my issues actually.
Basically:
In the discovery step of this Airtable Source, Airbyte gets the schema from the first record returned by Airtable. The problem is, Airtable doesn't return the field in the GET request if the field is empty, even though this field might have a value in another record. This leads to a lot of fields missing from the schema on Airbyte.
And my guess is that this can be solved, if we do the discovery step using the Metadata API

[misteryeo]

Hi @patygold, yes we understand that the Metadata API would solve this issue but unfortunately, we haven't been able to get access from the Airtable team. If you have someone on the Airtable side you could put us in touch with who could possibly get us access, we'd love to move forward here!

cc: @YowanR

[bazarnov]

Hi @patygold, yes we understand that the Metadata API would solve this issue but unfortunately, we haven't been able to get access from the Airtable team. If you have someone on the Airtable side you could put us in touch with who could possibly get us access, we'd love to move forward here!

cc: @YowanR

All we need is the account that has the access to the Metadata API, to reproduce and implement the new dynamic schemas.

But, this will force existing users to obtain the same level of access to be able to use the connector afterwards.

Or, optionally, we can try to make the change backwards capable

[patygold]

@misteryeo
I've asked around, and unfortunately, we do not have such a contact, only the general customer support. So, we are in the same situation, it seems.

[misteryeo]

Thanks for trying @patygold - we'll continue to find ways around this!

cc: @YowanR

[patygold]

Hey all! This morning I've received this email from Airtable. Now I think this might help us. The link: https://airtable.com/developers/web/api/changelog?utm_ID=recebVmIxYe5ut93c&utm_source=lifecycle_team&utm_medium=email&utm_campaign=it_ss_api_auth_public_beta&utm_content=email_waitlist-users&utm_extra5=metadata-api-users#anchor-2022-11-15

[YowanR]

This is exciting! Thanks for sharing, @patygold! We'll have the team look at this once we have the bandwidth