Re: Re: Re: Re: Re: Integrate PyHive and Alembic

[Ke Zhu - k...@us.ibm.com]

Mike,

Thanks for merging my change on Alembic!

On Thu, 2020-02-27 at 15:38 -0500, Mike Bayer wrote:
> 
> 
> On Thu, Feb 27, 2020, at 2:49 PM, Ke Zhu - k...@us.ibm.com wrote:
> > On Wed, 2020-02-26 at 11:07 -0500, Mike Bayer wrote:
> > > oh, that issue is talking about rowcount.  Alembic does not need
> > > rowcount to function correctly.I see that Alembic is doing
> > > this now, however there is a dialect-level flag called
> > > "supports_sane_rowcount", if this were False, Alembic should be
> > > checking this and skipping that particular check.
> > 
> > I see. it would be great if Alembic will respect the
> > "support_sane_rowcount" specified in a SQLAlchemy dialect when
> > updating/deleting revision. If this is a suggested design, I can
> > patch it via a PR.
> 
> yes.
> 
> 
> > According to specific dialect, it just need to use this option
> > instead of returning "-1". I can document this in the issue 
> > https://github.com/dropbox/PyHive/issues/315
> 
> the pyhive dialect advertises the attribute correctly right?

That's the fix I proposed in that issue.

> 
> > > is the "rowcount" check the only issue here?   this part can be
> > > changed on the Alembic side.
> > 
> > Besides the above issue, I want to get input on 
> > https://github.com/dropbox/PyHive/issues/314 as well. How could I
> > use SQLAlchemy API to create table like what this sql does:
> > CREATE TABLE hello_acid (key int, value int)
> > STORED AS ORC TBLPROPERTIES ('transactional'='true');
> > I've no idea how to make `STORED AS and TBLPROPERTIES` part for a
> > Hive table. Now I just use SQLAlchemy compiler to append them for
> > CreateTable.
> 
> There's a few paths to make this work depending on where and how far
> you want to go, or at least how this works.
> 
> 
> Assuming these are *optional* arguments for the tables that you
> create with the pyhive dialect, the official way is that the dialect
> can be made to support these (I thought we had a generic "suffixes"
> section right now, but we don't).   The way they work can be seen in
> the Postgresql dialect.  start with the keywords you want, here I
> might call it pyhive_stored_as="ORC" 
> pyhive_tblproperties="('transactional'='true').  then they'd be set
> up as "stored_as" and "tblproperties" in the dialect's
> construct_arguments like Postgresql does here: 
> 
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/base.py#L2407
> 
> then you consume these in post_create_table:
> 
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/base.py#L2037
> 
> 
> if you need Alembic's alembic_version to have these keywords, im not
> sure we have a keyword for that right now however you can use
> metadata events to intercept when alembic_version is built up, or you
> can intercept the compiler as you're doing now.
> 
> if alternatively all pyhive tables need these keywords
> unconditionally, then you'd just implement post_create_table in the
> compiler to add them in.
> 

Thanks for the pointers! It's very useful. I will try these paths since
not all Hive tables need such keywords so far. It depends on how data
is ingested in Hive/hadoop.

> 
> > 
> > 
> > 
> > --
> > You received this message because you are subscribed to the Google
> > Groups "sqlalchemy-alembic" group.
> > To unsubscribe from this group and stop receiving emails from it,
> > send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> > To view this discussion on the web visit 
> > https://groups.google.com/d/msgid/sqlalchemy-alembic/ab42854af49a98c83fd3693c92dbd9ad5c59b9df.camel%40us.ibm.com
> > .
> 
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy-alembic/c1186fc5514dda850a9ae08e61624b765cc8d686.camel%40us.ibm.com.

Re: Re: Re: Re: Integrate PyHive and Alembic

[Mike Bayer]

On Thu, Feb 27, 2020, at 2:49 PM, Ke Zhu - k...@us.ibm.com wrote:
> On Wed, 2020-02-26 at 11:07 -0500, Mike Bayer wrote:
>> oh, that issue is talking about rowcount. Alembic does not need rowcount to 
>> function correctly. I see that Alembic is doing this now, however there is a 
>> dialect-level flag called "supports_sane_rowcount", if this were False, 
>> Alembic should be checking this and skipping that particular check.
> 
> I see. it would be great if Alembic will respect the "support_sane_rowcount" 
> specified in a SQLAlchemy dialect when updating/deleting revision. If this is 
> a suggested design, I can patch it via a PR.

yes.


> 
> According to specific dialect, it just need to use this option instead of 
> returning "-1". I can document this in the issue 
> https://github.com/dropbox/PyHive/issues/315

the pyhive dialect advertises the attribute correctly right?

> 
>> is the "rowcount" check the only issue here? this part can be changed on the 
>> Alembic side.
> 
> Besides the above issue, I want to get input on  
> https://github.com/dropbox/PyHive/issues/314 as well. How could I use 
> SQLAlchemy API to create table like what this sql does:
> CREATE TABLE hello_acid (key int, value int)
STORED AS ORC TBLPROPERTIES ('transactional'='true');
> I've no idea how to make `STORED AS and TBLPROPERTIES` part for a Hive table. 
> Now I just use SQLAlchemy compiler to append them for CreateTable.

There's a few paths to make this work depending on where and how far you want 
to go, or at least how this works.


Assuming these are *optional* arguments for the tables that you create with the 
pyhive dialect, the official way is that the dialect can be made to support 
these (I thought we had a generic "suffixes" section right now, but we don't). 
The way they work can be seen in the Postgresql dialect. start with the 
keywords you want, here I might call it pyhive_stored_as="ORC" 
pyhive_tblproperties="('transactional'='true'). then they'd be set up as 
"stored_as" and "tblproperties" in the dialect's construct_arguments like 
Postgresql does here: 

https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/base.py#L2407

then you consume these in post_create_table:

https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/base.py#L2037


if you need Alembic's alembic_version to have these keywords, im not sure we 
have a keyword for that right now however you can use metadata events to 
intercept when alembic_version is built up, or you can intercept the compiler 
as you're doing now.

if alternatively all pyhive tables need these keywords unconditionally, then 
you'd just implement post_create_table in the compiler to add them in.



> 
> 
> 

> --
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy-alembic" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy-alembic/ab42854af49a98c83fd3693c92dbd9ad5c59b9df.camel%40us.ibm.com
>  
> .

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy-alembic/a1d41899-547d-4399-95ab-bb4c31c04feb%40www.fastmail.com.

Re: Re: Re: Re: Integrate PyHive and Alembic

[Ke Zhu - k...@us.ibm.com]

On Wed, 2020-02-26 at 11:07 -0500, Mike Bayer wrote:
oh, that issue is talking about rowcount.  Alembic does not need rowcount to 
function correctly.I see that Alembic is doing this now, however there is a 
dialect-level flag called "supports_sane_rowcount", if this were False, Alembic 
should be checking this and skipping that particular check.

I see. it would be great if Alembic will respect the "support_sane_rowcount" 
specified in a SQLAlchemy dialect when updating/deleting revision. If this is a 
suggested design, I can patch it via a PR.

According to specific dialect, it just need to use this option instead of 
returning "-1". I can document this in the issue 
https://github.com/dropbox/PyHive/issues/315

is the "rowcount" check the only issue here?   this part can be changed on the 
Alembic side.

Besides the above issue, I want to get input on 
https://github.com/dropbox/PyHive/issues/314 as well. How could I use 
SQLAlchemy API to create table like what this sql does:

CREATE TABLE hello_acid (key int, value int)
STORED AS ORC TBLPROPERTIES ('transactional'='true');

I've no idea how to make `STORED AS and TBLPROPERTIES` part for a Hive table. 
Now I just use SQLAlchemy compiler to append them for CreateTable.


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy-alembic/ab42854af49a98c83fd3693c92dbd9ad5c59b9df.camel%40us.ibm.com.

Re: Re: Re: Integrate PyHive and Alembic

[Mike Bayer]

On Tue, Feb 25, 2020, at 11:06 AM, Ke Zhu - k...@us.ibm.com wrote:
> According to Apache Hive, Update/Delete can only be performed on tables that 
> supports ACID. see  
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Update
> 
> > this is a database that can change schema structures but not 
> > insert/update/delete rows? is there some kind of SQL layer that has to be 
> > in use that isn't there when you create structures?
> 
> Yes! Basically it separate data store and data model so that you can update 
> table schema as metadata while the actual data can be loaded/streamed into 
> external data store w/o SQL interface.
> 
> > that wouldn't be very easy but also it would be way better to store the 
> > version info in the target DB itself.
> 
> totally understand this. I see this design as advantage as well just like 
> other schema management tool like sqitch. I'm just looking for possiblilities 
> since I've seen it uses the same connection to execute schema changes and 
> versioning change in a migration context.
> 
> > I don't know anything about Hive/Presto, but they *are* databases so I'd 
> > assume you can put data in them
> 
> Yes to Hive, when using PyHive, it just needs to fix  
> https://github.com/dropbox/PyHive/issues/315

oh, that issue is talking about rowcount. Alembic does not need rowcount to 
function correctly. I see that Alembic is doing this now, however there is a 
dialect-level flag called "supports_sane_rowcount", if this were False, Alembic 
should be checking this and skipping that particular check.

is the "rowcount" check the only issue here? this part can be changed on the 
Alembic side.


> 
> While, the answer will be no to PrestoSQL which is just a SQL query engine 
> that delegates data model and data store to query targets 
> (MySQL/Postgres/Kafka/Elasticsearch etc) via connectors.
> 
> On Mon, 2020-02-24 at 18:28 -0500, Mike Bayer wrote:
>> 
>> 
>> On Mon, Feb 24, 2020, at 3:44 PM, Ke Zhu - k...@us.ibm.com wrote:
>>> Mike,
>>> 
>>> Thanks for the pointers. I've figured out the programming part and 
>>> discovers more things when integrating PyHive:
>>> 
>>> 1. It requires the table alembic_version to be transactional so that it can 
>>> do Update/Delete when upgrade/downgrade. which is challege for Hive3 which 
>>> has limited ACID support. Since I could not figure out a way to program a 
>>> transactional table via SQLAlechemy API (see 
>>> https://github.com/dropbox/PyHive/issues/314), it ended up a decoration to 
>>> patch sqlalchemy.schema.CreateTable by appending table properties.
>> 
>> it's not a hard requirement that there's a DB transaction in use, you could 
>> run alembic in an "autocommit" mode and that shouldn't cause any problem. 
>> you just won't be able to roll back if something fails. im not completely 
>> sure what you mean by "the table to be transactional so that it can do 
>> update/delete" but from Alembic's point of view it just needs to run 
>> INSERT/UPDATE/DELETE but there doesn't have to be any ACID guarantees.
>> 
>> 
>>> 2. PyHive doesn't fully support Update/Delete for Hive/Presto yet. it's 
>>> easy to patch PyHive but the key problem is it doesn't support 
>>> transactional DDL like rollback in Hive3.
>>> 
>>> Although I've managed to get `alembic upgrade` and `alembic downgrade` to 
>>> work on Hive 3, it's still not a fully transactional experience (e.g., 
>>> changed schema sccessfully but failed to update table alembic_version).
>> 
>> so..this is a database that can change schema structures but not 
>> insert/update/delete rows? im not following. is there some kind of SQL layer 
>> that has to be in use that isn't there when you create structures?
>> 
>> 
>>> 
>>> I wonder if there's any design direction in Alembic to allowing storing the 
>>> version table `alembic_version` in another db when dealing with non-RDBMS 
>>> SQL engine (Hive/Presto). e.g., supporting a postgres db to store table 
>>> alembic while delivering the actual changes to Hive. I had a PoC to using 
>>> multi-db template to manage the table `alembic_version` in a RDBMS while 
>>> denying any operation on table `alembic_version` in Hive/Presto. it works 
>>> now but does it sound right?
>> 
>> that wouldn't be very easy but also it would be way better to store the 
>> version info in the target DB itself. I don't know anything about 
>> Hive/Presto, but they *are* databases so I'd assume you can put data in them.
>> 
>> 
>>> 
>>> On Sat, 2020-01-25 at 18:19 -0500, Mike Bayer wrote:
 
 
 On Fri, Jan 24, 2020, at 1:56 PM, Ke Zhu wrote:
> Just discovered this post when trying to do exact same thing (besides 
> planning to support one more dialect).
> 
> > Anywhere in your hive dialect, simply put the above code that you have 
> > (using the correct imports of course). 
> 
> Does it mean it must introduce dependency to alembic (since it uses 
> alembic.ddl.impl.DefaultImpl) in a p

Re: Re: Re: Integrate PyHive and Alembic

[Ke Zhu - k...@us.ibm.com]

According to Apache Hive, Update/Delete can only be performed on tables that 
supports ACID. see 
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Update

> this is a database that can change schema structures but not 
> insert/update/delete rows? is there some kind of SQL layer that has to be in 
> use that isn't there when you create structures?

Yes! Basically it separate data store and data model so that you can update 
table schema as metadata while the actual data can be loaded/streamed into 
external data store w/o SQL interface.

> that wouldn't be very easy but also it would be way better to store the 
> version info in the target DB itself.

totally understand this. I see this design as advantage as well just like other 
schema management tool like sqitch. I'm just looking for possiblilities since 
I've seen it uses the same connection to execute schema changes and versioning 
change in a migration context.

> I don't know anything about Hive/Presto, but they *are* databases so I'd 
> assume you can put data in them

Yes to Hive, when using PyHive, it just needs to fix 
https://github.com/dropbox/PyHive/issues/315

While, the answer will be no to PrestoSQL which is just a SQL query engine that 
delegates data model and data store to query targets 
(MySQL/Postgres/Kafka/Elasticsearch etc) via connectors.

On Mon, 2020-02-24 at 18:28 -0500, Mike Bayer wrote:


On Mon, Feb 24, 2020, at 3:44 PM, Ke Zhu - k...@us.ibm.com wrote:
Mike,

Thanks for the pointers. I've figured out the programming part and discovers 
more things when integrating PyHive:

1. It requires the table alembic_version to be transactional so that it can do 
Update/Delete when upgrade/downgrade. which is challege for Hive3 which has 
limited ACID support. Since I could not figure out a way to program a 
transactional table via SQLAlechemy API (see 
https://github.com/dropbox/PyHive/issues/314),
 it ended up a decoration to patch sqlalchemy.schema.CreateTable by appending 
table properties.

it's not a hard requirement that there's a DB transaction in use, you could run 
alembic in an "autocommit" mode and that shouldn't cause any problem.  you just 
won't be able to roll back if something fails.   im not completely sure what 
you mean by "the table to be transactional so that it can do update/delete" but 
from Alembic's point of view it just needs to run INSERT/UPDATE/DELETE but 
there doesn't have to be any ACID guarantees.


2. PyHive doesn't fully support Update/Delete for Hive/Presto yet. it's easy to 
patch PyHive but the key problem is it doesn't support transactional DDL like 
rollback in Hive3.

Although I've managed to get `alembic upgrade` and `alembic downgrade` to work 
on Hive 3, it's still not a fully transactional experience (e.g., changed 
schema sccessfully but failed to update table alembic_version).

so..this is a database that can change schema structures but not 
insert/update/delete rows?  im not following.  is there some kind of SQL layer 
that has to be in use that isn't there when you create structures?



I wonder if there's any design direction in Alembic to allowing storing the 
version table `alembic_version` in another db when dealing with non-RDBMS SQL 
engine (Hive/Presto). e.g., supporting a postgres db to store table alembic 
while delivering the actual changes to Hive. I had a PoC to using multi-db 
template to manage the table `alembic_version` in a RDBMS while denying any 
operation on table `alembic_version` in Hive/Presto. it works now but does it 
sound right?

that wouldn't be very easy but also it would be way better to store the version 
info in the target DB itself.   I don't know anything about Hive/Presto, but 
they *are* databases so I'd assume you can put data in them.



On Sat, 2020-01-25 at 18:19 -0500, Mike Bayer wrote:


On Fri, Jan 24, 2020, at 1:56 PM, Ke Zhu wrote:
Just discovered this post when trying to do exact same thing (besides planning 
to support one more dialect).

> Anywhere in your  hive dialect, simply put the above code that you have 
> (using the correct imports of course).

Does it mean it must introduce dependency to alembic (since it uses 
alembic.ddl.impl.DefaultImpl) in a package (.e.g, pyHive) that supports 
sqlalchemy interfaces?

well you have to put it in a try/except ImportError block so that if alembic 
isn't installed, it silently passes.   there's a github issue to add support 
for real entrypoints but it hasn't been that critical.


If not, is there any guidance to support this at alembic level in a plug-gable 
way? E.g., declare a HiveImpl class in `env.py` of a project uses alembic?

you could put one in your env.py also but if you are the person working on the 
dialect y