Athena database connector

[nzoschke]

AWS Athena would be a nice addition to the supported databases. Athena is a managed service that enables making SQL queries to data stored in S3. Under the hood it uses Presto.

You can connect to Athena with JDBC or ODBC drivers so maybe #245 would get us there.

Or you can use AWS credentials and the Athena API to issue StartQueryExecution and GetQueryResults calls.

[nzoschke]

In looking at the code it seems like using the Athena API via the AWS SDK might be the easiest as it should be possible implement connect, query, etc. through the API.

[n-riesco]

I can't thank you enough for all the links!

I also think the Athena API is the way to go (the other options require distributing binaries and this increases noticeably the size of Falcon itself).

I'm planning to write a wiki page describing what's needed to integrate a DB connector into Falcon. At the moment the relevant code is spread across several files.

[nzoschke]

Thanks for the feedback. API route it is.

Yes, with some better guidance or docs I would take a crack at this myself.

In addition to the database connector I haven’t figured out how to build the app from source on Mac. Are there some docs on that I’m missing?

[n-riesco]

The only docs we've got at the moment is in https://github.com/plotly/falcon-sql-client/blob/master/CONTRIBUTING.md

For mac, you can build and run the desktop app like this:

$ yarn install
$ yarn run rebuild:modules:electron
$ rm -rf dist
$ yarn run build
$ yarn start

[nzoschke]

Ok I missed that doc. Thank you for the pointer

[n-riesco]

I will document this at some point, but here's a list of files and folders that may need updating to implement a new connector:

• backend/persistent/datastores/Datastores.js
• app/constants/constants.js
• app/images
• app/actions/sessions.js
• app/components/Settings/Tabs/Tab.react.js
• app/components/Settings/Settings.react.js

BTW, I've also noticed that the S3 connector is already using AWS SDK.

[chriddyp]

Also see https://github.com/plotly/falcon-sql-client/pull/203/files for an example of a PR that introduced a new data connector (Imapala) within the last couple of months.

[shannonlal]

Were you able to get the latest build environment to build from source @nzoschke? I noticed above you said that you had a problem and I wanted to see if you were able to get it to build from source. I am currently having an issue and wanted to see if anyone is running into the same issue.

[shannonlal]

Is anyone still looking at putting this together? If not I would be willing to take a stab at it. We have just starting using Athena and are looking for a Graphing tool that would allow us to build some reporting charts.

Also, I looked at the AWS SDK and the Athena class looks like you need to have some retry logic. I looked at the following npm package Node-Athena: https://github.com/KoteiIto/node-athena and it looks like it could be a great starting place to atleast get some basic functionality. This would be pretty straight forward to implement and we could follow the SQL.js backend datastore file as a template

[n-riesco]

Is anyone still looking at putting this together? If not I would be willing to take a stab at it.

I'm not aware of anyone working on this connect.
@nzoschke , are you working on this?

I looked at the following npm package Node-Athena: https://github.com/KoteiIto/node-athena and it looks like it could be a great starting place to atleast get some basic functionality.

I had a quick look and athena-client uses async functions internally; so you may need babel and webpack to get this working.

If this is a problem, you could use AWS.Athena directly. A simply retry logic could do for an initial PR.

[nzoschke]

I have not been working on it.

[shannonlal]

Ok. I will take this on. If I have any questions about how things should be put together I will post them here.

[shannonlal]

FYI. I have put together the stubbed code for this (See issue #266) and documented the steps for creating a new Datasource. I looked through the above node-athena library and doesn't look like it will meet our needs so I will using the pure AWS Javascript libraries instead. I will keep you posted

[shannonlal]

FYI. I have got the backend working for this. Implemented this using the AWS SDK so there are no new external dependencies. I am just trying to get everything integrated into front end and then I will push this up. I am hoping to put in a pull request in next week

[shannonlal]

@n-riesco I have this up and working and I am able to run queries from Electron front end to AWS Athena and see the results in the Charts. I have the tests passing and ESLint is passing. I just need to test a bit more some of the edge conditions and make sure the error handling is working correctly. I will be able to submit a pull request for this by the middle of this week. I had a couple of questions for you.

1. Do you want me to submit this on pull request on the master or would your prefer a separate branch?
2. All the other datasources use have real Plot.ly databases hosted on AWS. Is this something we want to do with Athena? Would you be able to set this up?

[n-riesco]

closed via #352