On 08/19/2016 06:37 AM, rla...@fastly.com wrote:
Hello all,
I have implemented the first stab at a PEP 249 adaptor layer for
BigQuery and it seems to work well. It is possible to create an
engine/connection/cursor, submit an SQL query and get results back (only
SELECT statements for now, API commands will come later). I have moved
on to changing the DDL and statement compilers to conform to BigQuery's
standard SQL dialect:
https://cloud.google.com/bigquery/sql-reference/query-syntax
I hit an immediate hurdle and I am not sure if it is because the Dialect
object I created is incorrect or because the PEP 249 adapter is behaving
in an unexpected way. Basically, upon connection SQL alchemy will fire
some test queries (afaiu to detect whether column names support
unicode), one of which is being rendered as
SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
The problem is that BigQuery does not support VARCHAR. I have already
added a colspecs dictionary to my new dialect object, with many common
data types mapped to their BgQuery equivalents:
The method that's calling this test and others is in
sqlalchemy/engine/default -> DefaultDialect.initialize(). You should
override that whole method and do away with all the things it's trying
to check there, as calchipan does:
https://bitbucket.org/zzzeek/calchipan/src/86ef380c572b9c1b8186278446a9b4952a538f97/calchipan/base.py?at=master&fileviewer=file-view-default#base.py-45
Although I would say that on the SQLAlchemy side,
_check_unicode_returns() should likely be a method that can raise
NotImplementedError() individually like the rest of the tests called
within the base initialize().
colspecs = {
types.Unicode: BQString,
types.Integer: BQInteger,
types.SmallInteger: BQInteger,
types.Numeric: BQFloat,
types.Float: BQFloat,
types.DateTime: BQTimestamp,
types.Date: BQTimestamp,
types.String: BQString,
types.LargeBinary: BQBytes,
types.Boolean: BQBoolean,
types.Text: BQString,
types.CHAR: BQString,
types.TIMESTAMP: BQTimestamp,
types.VARCHAR: BQString
}
I was under the impression that this would be enough to define a
behaviour where sqlalchemy queries using e.g. VARCHAR would be compiled
using the BQString class, which should render as 'STRING' as defined in
its get_col_spec method. This is in accordance to BigQuery's basic type
system:
https://cloud.google.com/bigquery/sql-reference/data-types
However, the query renders as above and the underlying PEP 249 throws an
exception. I could however envisage a type of operation where 2 queries
a fired to the DB, one using VARCHAR and another using unicode, in order
to detect which one succeeds. If this is the case, maybe the query
rendering is fine and the problem is the underlying library returning an
exception instead of some standard failure signal.
Does anybody know if
1) defining a colspecs object as above will be enough for objects of
e.g. types.VARCHAR to be rendered as e.g. 'STRING' in generated SQL?
2) Does the underlying PEP 249 implementation need to signal failure in
a particular way, or is throwing exceptions the expected behaviour?
Thanks,
Raul
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
