Add support for Google Cloud Bigtable in SQLAlchemy

[arita37]

It would be very useful to have Google Bigtable support in Python SQLAlchemy.

It seems Cloud MySQL is fine since SQL Alchemy supports it. However, for NoSQL tables, it seems there is not yet support from Google Bigtable.

Also, Google Big Query should have compliant interface with SQL Alchemy.
#2434

It will really help to develop new applications from Bigtable.

[mbrukman]

The integration between SQLAlchemy and Google Cloud Bigtable would have to be done in SQLAlchemy. I was going to file a bug on SQLAlchemy on your behalf, but looks like you've already filed a feature request and it was closed as wontfix:

unfortunately Google bigtable is non-relational and non-SQL, SQLAlchemy does not have support for key/value stores.

and a previous email thread on the sqlalchemy@ list about adding support for NoSQL databases like HBase (which is very similar to Bigtable) ended up without any answers.

Thus, I am afraid we won't be able to help you use SQLAlchemy together with Bigtable.

---

That said, as an alternative, consider using Apache Hue, which works with Apache HBase, and can be made to work similarly with Bigtable. We don't have a simple howto for how to connect Apache Hue to Cloud Bigtable yet, but I imagine it can be done as follows:

1. Apache Hue -> (a: Thrift API) -> Apache HBase Thrift proxy -> (b: gRPC API) -> Google Cloud Bigtable

   The first connection (a) should work out-of-the-box for Hue and HBase. The second connection (b) can use the Google Cloud Bigtable Java client for HBase. This is not as complicated as it looks, although there are several parts to connect together to make it all work.

2. Apache Hue -> (gRPC API) -> Google Cloud Bigtable

   This could be done using the Google Cloud Bigtable Java client for HBase, but it requires Apache Hue to use the HBase 1.x API (which I believe is not yet the case, I believe it's using 0.9x API and/or Thrift), so I would recommend following option (1) above for now instead.

Hope this is helpful.

[arita37]

Ok, Thanks for SQL Alchemy feedback.

What about integrating Google Big Table into Blaze Python ?
They are using Impala integrator like this one :

PY Impala
https://github.com/cloudera/impyla

Python client for HiveServer2 implementations (e.g., Impala, Hive) for distributed query engines.
Features
HiveServer2 compliant; works with Impala and Hive, including nested data

Fully DB API 2.0 (PEP 249)-compliant Python client (similar to sqlite or MySQL clients) supporting Python

Works with Kerberos, LDAP, SSL
SQLAlchemy connector
Converter to pandas DataFrame, allowing easy integration into the Python data stack (including scikit-learn and matplotlib); but see the Ibis project for a richer experience

[mbrukman]

Integrating Apache Impala with Cloud Bigtable certainly makes sense, and should enable the other integrations you are looking for (Baze in particular).

Looks like Apache Impala already works with Apache HBase so, in theory, it should also be compatible with Cloud Bigtable via the Cloud Bigtable HBase-compatible Java client library.

Please feel free to try using the Cloud Bigtable Java client for HBase with Apache Impala (and Apache Hive, since it provides the metadata store for Impala) and if you run into any issues, please let us know and also file a bug on Apache Impala and/or Apache Hive, as appropriate.

[arita37]

Hello, We are on Python side, so we do not use Java Also this Github is only for Google Python. It would be good that Google supports better Python (as Amazon is doing it already).

[mbrukman]

@arita37, I think we're talking about the same thing, but perhaps I was unclear. Let me clarify what I think you're looking for and how to accomplish your goal.

As I understand it (please correct me if I'm wrong), you would like to connect the following set of components:

Blaze → Impyla → Apache Impala → Google Cloud Bigtable

What I am suggesting is that to accomplish last connection (Impala → Bigtable), since Impala is written in Java and integrates with Apache HBase, you need to:

1. download the Cloud Bigtable HBase-compatible Java client library
2. modify /etc/impala/conf/hbase-site.xml as described on this page

You don't need to write any Java code to do this, just change configuration in an XML file. We are not suggesting that you should switch from Python to Java for your development.

I hope this helps. Let us know if you run into any issues with this setup, configuration, or performance.

[mbrukman]

@arita37, we recently launched support for BigQuery to query data stored in Cloud Bigtable, and BigQuery supports SQL. It removes the need to run Impala or Hive or any other system, so anything that can already talk to BigQuery can use this (read-only) bridge to query data in Cloud Bigtable.

Would this address your use case?

[mbrukman]

@daspecster, @dhermes -- not sure why it says I removed my assignment, I did not do that (only left a comment) and had no intention of doing this. However, I am now unable to reassign this issue to myself (looks like I don't have permission to do so). Please feel free to assign it back to me. Thanks!

[lukesneeringer]

I think you lost your assignment because you are not on a team with write access to this repository. We did a pretty thorough permissions scrub a few weeks ago.

Poke me on Hangouts if you actively need write access; otherwise, feel free to just treat this issue as if it is yours and send a pull request.

[mbrukman]

@lukesneeringer – I don't have a strong need for repo-wide write access; I just wanted to keep this assigned to me for clarity and tracking purposes. There isn't a specific PR to send at this time. If you have repo write access, maybe you can assign it to me?

[dhermes]

From Assigning issues and pull requests to other GitHub users:

If you have write access to a repository, you can assign issues and pull requests to yourself, collaborators on personal projects, or members of your organization with read permissions on the repository.

@lukesneeringer Was under the impression you needed write access to be assigned but it looks like read access will be sufficient?

[lukesneeringer]

I think at this point, I am going to close this issue. The real need now is to integrate BigQuery with SQLAlchemy, which is tracked in #3023.