Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17212

Connect Engine ODBC Table_Type - Discovery Process

Details

    Description

      Some minor issues when using MariaDB's Connect ODBC Table_Type Engine with Microsoft Parallel Data Warehouse (PDW):

      table_options: TABNAME

      When using a Table Definition such as the following for importing a Table:

      USE db_name;
       
      CREATE OR REPLACE TABLE `t_name_connect`
        ENGINE=CONNECT
        TABLE_TYPE=ODBC 
        CONNECTION='dsn_name'
       OPTION_LIST='UseDSN=Yes,User=db_user_name,Password=********'
        TABNAME='db_name.schema_name.table_name';
       
      CREATE OR REPLACE TABLE t_name_mariadb
      AS
        SELECT
          *
        FROM
          t_name_connect;
      

      The Query works perfectly (10/10), and I am able to import my tables easily to MariaDB. However, an observed limitation is that in TABNAME option, I can only specify my table as:

      • db_name.schema_name.table_name.
      • And not [db name].[schema name].[table name], which is common syntax for database / schema / table with spaces in TRANSACT-SQL.

      table_options: SRCDEF

      With Microsoft PDW, using "SQL Server Native Client 11.0" it is not possible to use SRCDEF table option, as otherwise, we would obtain error:

      "SQLNumResultCols: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint".

      This limitation could be avoided with first method, but we are not able create table definitions as the following by means of the Table Discovery Process:

      CREATE OR REPLACE TABLE `t_SQLServer_Table`
        ENGINE=CONNECT
        TABLE_TYPE=ODBC 
        CONNECTION='dns_name'
        OPTION_LIST='UseDSN=Yes,User=user_name,Password=*****'
        SRCDEF='
          SELECT
            *
          FROM
            [db_name].[schema_name].[table_name]
        ';
      

      Previous Table definition would only work if we specified manually the Column Definitions.

      Datetime Field Precission

      Another issue is Datetime Precission (CONNECT Data Types), which can also be handled with querys such as:

      CREATE OR REPLACE TABLE `t_name_connect`
      (
        `Col_Name` VARCHAR(255)
      )
        ENGINE=CONNECT
        TABLE_TYPE=ODBC 
        CONNECTION='dsn_name'
        OPTION_LIST='UseDSN=Yes,User=user_name,Password=******'
        TABNAME='db_name.sch_namel.tb_name';
       
      CREATE OR REPLACE TABLE t_name_mariadb
      AS
        SELECT
          STR_TO_DATE(
            `Col_Name`,
            '%Y-%m-%d %H:%i:%S.%f' /* datetime2: YYYY-MM-DD HH:MM:SS.fff */
          ) AS `Col_Name`
        FROM
          `t_name_connect`;
      

      Although a little verbose, works.

      So:

      1. Could TABNAME accept `[`, `]` Characters?
      2. Any way to make work SRCDEF with Microsoft PDW and the Discovery Process?
      3. Any more comfortable way to handle dates in the way shown without losing precision?

      Attachments

        Activity

          Juan Juan Telleria added a comment -

          Thank you for the Fix.

          I attached the PDF Documents of Connect Engine Handler to the MariaDB Knowledge base Article:

          https://mariadb.com/kb/en/library/using-connect-written-documentation/

          Hope others find them useful

          In addition, on the MariaDB github repository, where is the Connect Internal Data Types Definition located? E.g: TYPE_STRING, TYPE_INT, TYPE_DATE, ...

          Juan Juan Telleria added a comment - Thank you for the Fix. I attached the PDF Documents of Connect Engine Handler to the MariaDB Knowledge base Article: https://mariadb.com/kb/en/library/using-connect-written-documentation/ Hope others find them useful In addition, on the MariaDB github repository, where is the Connect Internal Data Types Definition located? E.g: TYPE_STRING, TYPE_INT, TYPE_DATE, ...
          Juan Juan Telleria added a comment - - edited

          In addition, on the MariaDB github repository, where is the Connect Internal Data Types Definition located? E.g: TYPE_STRING, TYPE_INT, TYPE_DATE, ...

          Seems to be on:
          https://github.com/MariaDB/server/tree/10.4/storage/connect

          But cannot find the specific file...

          Juan Juan Telleria added a comment - - edited In addition, on the MariaDB github repository, where is the Connect Internal Data Types Definition located? E.g: TYPE_STRING, TYPE_INT, TYPE_DATE, ... Seems to be on: https://github.com/MariaDB/server/tree/10.4/storage/connect But cannot find the specific file...

          In global.h

          bertrandop Olivier Bertrand added a comment - In global.h
          Juan Juan Telleria added a comment - - edited

          Does the already released MariaDB 10.3.11 implement the fix? Or will we have to wait till 10.3.12?

          I think the commit has already been pushed... But not sure.

          Thank you!

          Juan

          Juan Juan Telleria added a comment - - edited Does the already released MariaDB 10.3.11 implement the fix? Or will we have to wait till 10.3.12? I think the commit has already been pushed... But not sure. Thank you! Juan

          It's hard to tell. I'm not the one who includes updates in new releases and sometimes CONNECT was forgotten.

          bertrandop Olivier Bertrand added a comment - It's hard to tell. I'm not the one who includes updates in new releases and sometimes CONNECT was forgotten.

          People

            bertrandop Olivier Bertrand
            Juan Juan Telleria
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.