SAS pass through - Extract from MySQL does not work

Question

I'm trying to build a Data Integration job uses pass through to extract data from a view in a MySQL database. Wev'e been using pass through a lot in the project, mostly extracting data from Redshift, however with MySQL I was not able to do make it work properly.

It keeps complaining a table is missing even though when pass through is off, view is found and data is extracted...

tried every trick I know, starting from enabling case-sensitive DBMS object names, to manually remove single/double quotes from the statement just in case MySQL confuses confuses it with something else...

No luck. ODBC driver is [MySQL][ODBC 5.3(a) Driver][mysqld-5.5.53]. Ran on a Windows environment.

Any idea how to solve this?

Thank you in advance.

EDIT

So, first of all, one correction (even though not that important - I extract from a view, not a table).

This is the code generated by SAS Create Table transformation, pass through enabled. I only put an asterisk instead of the full list of columns:

proc sql;
  connect to ODBC
  ( 
    READBUFF=10000 DATASRC="cmp.web_api" AUTHDOMAIN="MYSQL_CMP_Auth" 
  ); 
  create table work."W7ZZZKOC"n as
    select
    *
    from connection to ODBC
     (
      select
      V_BI_ACCOUNT.ACCOUNT_NAME,
      V_BI_ACCOUNT.ACQUISITION_SOURCE__C,
      V_BI_ACCOUNT.ZUORA__ACTIVE__C,
      V_BI_ACCOUNT.ADDRESS_LINE_1__C,
      V_BI_ACCOUNT.ADDRESS_LINE_2__C,
      V_BI_ACCOUNT.ADDRESS_LINE_3__C,
      V_BI_ACCOUNT.AGREEMENT_DATE,
      V_BI_ACCOUNT.AGREEMENT_LEGAL_CLAUSE_1__C,
      V_BI_ACCOUNT.AGREEMENT_LEGAL_CLAUSE_2__C,
      V_BI_ACCOUNT.PERSONBIRTHDATE,
      V_BI_ACCOUNT.BLOCKED_REASON__C,
      V_BI_ACCOUNT.BRAND__C,
      V_BI_ACCOUNT.CPN__C,
      V_BI_ACCOUNT.ACCCREATEDBYID,
      V_BI_ACCOUNT.ACCCREATEDDATE,
      V_BI_ACCOUNT.CURRENCY_PREFERENCE__C,
      V_BI_ACCOUNT.CUSTOMER_FULL_NAME__PC,
      V_BI_ACCOUNT.ACCOUNTID,
      V_BI_ACCOUNT.ZUORA__CUSTOMERPRIORITY__C,
      V_BI_ACCOUNT.DELIVERY_SALUTATION__C,
      V_BI_ACCOUNT.DISPLAY_NAME,
      V_BI_ACCOUNT.PERSONEMAIL,
      V_BI_ACCOUNT.EMAILKEY__C,
      V_BI_ACCOUNT.FACEBOOKKEY,
      V_BI_ACCOUNT.FIRSTNAME,
      V_BI_ACCOUNT.GENDER__C,
      V_BI_ACCOUNT.PHONE,
      V_BI_ACCOUNT.ACCLASTACTIVITYDATE,
      V_BI_ACCOUNT.ACCLASTMODIFIEDDATE,
      V_BI_ACCOUNT.LASTNAME,
      V_BI_ACCOUNT.OTHER_EMAIL__C,
      V_BI_ACCOUNT.PI_TYPE__C,
      V_BI_ACCOUNT.ACCPARENTID,
      V_BI_ACCOUNT.POSTCODE__C,
      V_BI_ACCOUNT.PRIMARY_ACCOUNT_OF_THIS_CUSTOMER,
      V_BI_ACCOUNT.ACCPRIMARY__C,
      V_BI_ACCOUNT.ACCREASON_FOR_STATUS__C,
      V_BI_ACCOUNT.ZUORA__SLA__C,
      V_BI_ACCOUNT.ZUORA__SLASERIALNUMBER__C,
      V_BI_ACCOUNT.SALUTATION,
      V_BI_ACCOUNT.ACCSYSTEMMODSTAMP,
      V_BI_ACCOUNT.PERSONTITLE,
      V_BI_ACCOUNT.ZUORA__UPSELLOPPORTUNITY__C,
      V_BI_ACCOUNT.X_CODE__C,
      V_BI_ACCOUNT.ZUORA__ACCOUNT_ID__C,
      V_BI_ACCOUNT.ZUORA__PAYMENTMETHODID__C,
      V_BI_ACCOUNT.CITY,
      V_BI_ACCOUNT.ORIGINAL_CREATED_DATE,
      V_BI_ACCOUNT.SOURCE_SYSTEM_ID,
      V_BI_ACCOUNT.STATUS,
      V_BI_ACCOUNT.ZUORA__CONTACT_ID,
      V_BI_ACCOUNT.ACCISDELETED,
      V_BI_ACCOUNT.BILLING_ACCOUNT_NAME,
      V_BI_ACCOUNT.ACZCREATEDDATE,
      V_BI_ACCOUNT.ACZSYSTEMMODSTAMP,
      V_BI_ACCOUNT.ACZLASTACTIVITYDATE,
      V_BI_ACCOUNT.ZUORA__ACCOUNT__C,
      V_BI_ACCOUNT.ZUORA__ACCOUNTNUMBER__C,
      V_BI_ACCOUNT.ZUORA__AUTOPAY__C,
      V_BI_ACCOUNT.ZUORA__BALANCE__C,
      V_BI_ACCOUNT.ZUORA__CREDITCARDEXPIRATION__C,
      V_BI_ACCOUNT.ZUORA__CURRENCY__C,
      V_BI_ACCOUNT.ZUORA__MRR__C,
      V_BI_ACCOUNT.ZUORA__PAYMENTTERM__C,
      V_BI_ACCOUNT.ZUORA__PURCHASEORDERNUMBER__C,
      V_BI_ACCOUNT.ZUORA__LASTINVOICEDATE__C,
      V_BI_ACCOUNT.COUNTRY_NAME,
      V_BI_ACCOUNT.COUNTRY_CODE,
      V_BI_ACCOUNT.FAVOURITE_FOOTBALL_CLUB,
      V_BI_ACCOUNT.COUNTY
      from
      web_api.V_BI_ACCOUNT as V_BI_ACCOUNT

    );

   %rcSet(&sqlrc); 

disconnect from ODBC; 
quit;

And again, when I extract data without pass through - works successfully,

Can you show a simplified version of a query that doesn't work? Make sure to test the query using some other tool to connect to mySQL to confirm that your mySQL syntax is correct. — Tom, Mar 13 '17 at 16:13
Random thought, but, is this table a temporary table of any nature? Any reason a different session would see it differently? — Joe, Mar 13 '17 at 17:24
And what Tom said - show us what's not working. In particular the name of the not-working table would be useful as it may be the issue itself. (For simplified example, in SQL Server, this would fail in passthrough: `select * from plan;` but would succeed in libname, since `plan` is a reserved word and needs `[]` - you could have similar issue). — Joe, Mar 13 '17 at 17:25
Hey @Joe, no, that's a view, not a temporary table of any kind — user2518751, Mar 14 '17 at 08:55
And if you leave full list of columns, what happens? And when you run this in mySQL directly, what happens? — Joe, Mar 14 '17 at 16:04

user2518751 · Answer 1 · 2017-03-15T12:46:46.667

I found out the problem was a column name exceeds 32 positions. As SAS supports up column names up to 32, the query fails to find PRIMARY_ACCOUNT_OF_THIS_CUSTOMER as the original column name is PRIMARY_ACCOUNT_OF_THIS_CUSTOMER__C.

EDIT

One more thing I found out is, MySQL doesn't like specifying schema name nor aliases. Therefore, From clause to only specify table name i.e : 'from v_bi_account' rather than 'web_api.v_bi_account'

and do not use aliases i.e use 'from v_bi_account' rather than 'from v_bi_account as v_bi_account'

Thank you guys so much for your help.

SAS pass through - Extract from MySQL does not work

1 Answers1