none
SSIS google-bigquery Simba ODBC - two scenarios RRS feed

  • Question

  • SSIS ODBC source will show preview of the first 200 row for both scenarios. But it will pull in Columns for only scenario one NOT scenario two. It seems caused by metadata confusion of the query output. Please help.

    Google BigQuery Scenarios one:

     

    WITH Example as

    (

      select 5 as x, 'foo' as y

      union all

      select 6 as x, 'bar' as y

    )

     

    SELECT * FROM Example;

    Google BigQuery Scenarios two:

     

    CREATE TEMP TABLE Example

    (

      x INT64,

      y STRING

    );

     

    INSERT INTO Example

    VALUES (5, 'foo');

     

    INSERT INTO Example

    VALUES (6, 'bar');

     

    SELECT * FROM Example;



    Saturday, May 23, 2020 2:26 PM

All replies

  • Hi JayZhizi,

    CTEs are usually better when:
    SQL Server can do a good job of estimating how many rows will come out of it, and the contents of what those rows will be, or
    When what comes out of the CTE doesn’t really influence the behavior of the rest of the query, or
    When you’re not sure what portions of the CTE’s data will actually be necessary for the rest of the query (because SQL Server can figure out what parts to execute, and what parts to simply ignore)

    Temp tables are usually better when:
    You have to refer to the output multiple times, or
    When you need to pass data between stored procedures, or
    When you need to break a query up into phases to isolate unpredictable components that dramatically affect the behavior of the rest of the query

    The following links will be helpful:

      What’s Better, CTEs or Temp Tables?

      SQL Server CTE vs Temp Table vs Table Variable Performance Test

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 25, 2020 8:47 AM
  • Thank you Mona for the comparisons.

    But my issue is Bigquery query not SQL Server query. SSIS ODBC source responds to two scenarios differently: one work and one not work. I hope there is a trick so that both will work with SSIS. 

    Monday, May 25, 2020 10:41 PM
  • Hi JayZhizi,

    It's better for you to use the scenarios one.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 26, 2020 9:33 AM
  • Hi, Mona,

    I try to use CTE as much as possible because temple table in query will not work with SSIS. But there is also issue with CTE. Some time queries get "Resources exceeded during ....." error in Bigquery. I even break a query into two parts to work with SSIS in production.

    Just found another issue with Bigquery ODBC in SSIS. Queries with LIKE keywork will not work. The work around is to use REGEXP_CONTAINS().

    Thank you.

    Saturday, May 30, 2020 12:34 AM
  • Sorry. Take back. REGEXP_CONTAINS() doesn't work either.
    Saturday, May 30, 2020 1:33 AM
  • RESOLUTION:

    BigQuery(BQ) Simba ODBC driver doesn't work with SSIS ODBC source when the BQ query has some complex structures.

    I have table creation access in company's some sandbox project in BQ where I created a dataset and views to house the complex queries. Each time at data pull the views are executed to pull from the PROD project.

    In SSIS ODBC source I simply use select * from VIEW and all works.

    Monday, June 1, 2020 4:23 PM
  • Hi ,

    Please remember to click "Mark as Answer" the responses that resolved your issue. 

    This can be beneficial to other community members reading this thread.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 3, 2020 5:36 AM