You are viewing a plain text version of this content. The canonical link for it is here.

Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2020/09/17 12:56:11 UTC

[GitHub] [airflow] shizidushu opened a new issue #10992: Transfer table/data from mssql to mssql

shizidushu opened a new issue #10992:
URL: https://github.com/apache/airflow/issues/10992


   **Description**
   
   Sync table from business database to datawarehouse database incrementally (both mssql).
   
   **Use case / motivation**
   
   I want to keep two database tables sync. One is the business database. Another one is the datawarehouse database. (They are installed separaterly, i.e., in different machine).
   
   It seems `GenericTransfer` it desinged to do this kind of task. But I see there is only insert in `DbApiHook` (which `OdbcHook` and `MsSqlHook` inherit from).
   
   I guess something like https://github.com/apache/airflow/pull/8625 is needed for mssql.


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] akocukcu commented on issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

akocukcu commented on issue #10992:
URL: https://github.com/apache/airflow/issues/10992#issuecomment-709450160


   @turbaszek I would like to work on this.


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] shizidushu commented on issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

shizidushu commented on issue #10992:
URL: https://github.com/apache/airflow/issues/10992#issuecomment-721537142


   @akocukcu Thank you for your post. I didn't realize there are problems with `MERGE` operation before. 
   
   In our case, we have a data warehouse using Sql Server, there are many merge in the current code base (low performance). I was considering refactor the etl with airflow in september. Now our team decide to switch to airflow/hive/spark from scratch. And now I'm struggling with how to do incremental etl with hive (without merge, insert overwrite partition approach).


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] akocukcu commented on issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

akocukcu commented on issue #10992:
URL: https://github.com/apache/airflow/issues/10992#issuecomment-721562330

@shizidushu for your case again I kindly suggest using "Control Table" pattern with a Staging table.
You can insert data directly (maybe bulk load) to **empty** Staging table.
After that since staging table and datawarehouse table are in the same server, upsert operation will complete at lightning speed.

BTW, I think in the same sense there are `preoperator` parameters inside a lot of transfer operators.
For example if you look at the definition of `mysql_preoperator` parameter of [PrestoToMySqlOperator](https://github.com/apache/airflow/blob/fdd9b6f65b608c516b8a062b058972d9a45ec9e3/airflow/providers/mysql/transfers/presto_to_mysql.py), it says:

> ["... typically use to truncate of delete in place of the data coming in ...".](https://github.com/apache/airflow/blob/fdd9b6f65b608c516b8a062b058972d9a45ec9e3/airflow/providers/mysql/transfers/presto_to_mysql.py#L42)

So basically, first you can truncate the staging table, second insert the (only new or updated) oltp data to staging table, finally you can upsert staging data to datawarehouse table.

Just like in the blog post, you can test that approach in your environment and data.

Independent from your case I can add upsert functionality by adding `MERGE` to `MsSqlHook`. But I have concerns, if folks use it for upserting large data.
Maybe I can add a comment just like they did in `GenericTransfer`:

> ["... This is meant to be used on small-ish datasets ..."](https://github.com/apache/airflow/blob/4e8f9cc8d02b29c325b8a5a76b4837671bdf5f68/airflow/operators/generic_transfer.py#L32)

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] akocukcu edited a comment on issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

akocukcu edited a comment on issue #10992:
URL: https://github.com/apache/airflow/issues/10992#issuecomment-721565457


   @shizidushu Also since you are planning to use Hive, [`HiveToMySqlOperator`](https://github.com/apache/airflow/blob/master/airflow/providers/apache/hive/transfers/hive_to_mysql.py) is a very elegant example (Which can be adapted to use with MsSql).


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] shizidushu commented on issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

shizidushu commented on issue #10992:
URL: https://github.com/apache/airflow/issues/10992#issuecomment-721753589


   @akocukcu It's time to close it. 


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] shizidushu commented on issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

shizidushu commented on issue #10992:
URL: https://github.com/apache/airflow/issues/10992#issuecomment-721686516


   @akocukcu Thanks for you suggestion.


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] akocukcu commented on issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

akocukcu commented on issue #10992:
URL: https://github.com/apache/airflow/issues/10992#issuecomment-721156021

@shizidushu I have been trying to create a proper sollution for this issue with my newborn :D (thats why it took so long).
Solution: The equvialent of `"REPLACE INTO"` or `"INSERT ... ON CONFLICT DO UPDATE"` in Sql Server is built-in [MERGE](https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql) operation.
I can provide that by adding `_generate_insert_sql` method into `MsSqlHook`. After that it will be available for upserting **("small")** data.

But in the description it is said that, this will be used for incremental load to datawarehouse. According to my experience, using MERGE directly on datawarehouse table may produce unwanted results (in terms of concurrency and performance). I have just written a [blog post](https://akocukcu.github.io/incremental-etl-pipeline-airflow.html) about this topic.
https://akocukcu.github.io/incremental-etl-pipeline-airflow.html
In a nutshell, we should know that it has specific drawbacks in wide usage.

My suggestion for incremental etl is to use `GenericTransfer` combined with Staging table and additional MsSqlHooks for executing procedures.

At last, I can add upsert functionality by using Merge.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] akocukcu commented on issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

akocukcu commented on issue #10992:
URL: https://github.com/apache/airflow/issues/10992#issuecomment-721565457


   @shizidushu Also since you are planning to use Hive, `[HiveToMySqlOperator]`(https://github.com/apache/airflow/blob/master/airflow/providers/apache/hive/transfers/hive_to_mysql.py) is a very elegant example (Which can be adapted to use with MsSql).


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] boring-cyborg[bot] commented on issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

boring-cyborg[bot] commented on issue #10992:
URL: https://github.com/apache/airflow/issues/10992#issuecomment-694212261


   Thanks for opening your first issue here! Be sure to follow the issue template!
   


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] shizidushu closed issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

shizidushu closed issue #10992:
URL: https://github.com/apache/airflow/issues/10992


   


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] akocukcu commented on issue #10992: Transfer table/data from mssql to mssql

Posted by GitBox <gi...@apache.org>.

akocukcu commented on issue #10992:
URL: https://github.com/apache/airflow/issues/10992#issuecomment-721712629


   @shizidushu you are welcome :)
   So do you think we should close this issue or add upsert functionality to `MsSqlHook`.


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org