368605_en-US

liliconcom

Snowflake and Arrays

I have a two part-er for a customer request. First, has anyone had success connecting JMP to Snowflake? There is a Snowflake ODBC driver so I assume its possible but curious if anyone has any personal experience. Second, does JMP support array data types when querying using an ODBC driver? Thanks.

Re: Snowflake and Arrays

HI Peter,

We can connect directly to Snowflake and have several customers doing this currently.
They currently are using QB with Snowflake.

We recommend the CData Snowflake ODBC Driver as other vendors drivers do not work properly.
If customers have any issues with connecting I recommend they contact technical support directly.

 

I know some databases such as Google Big Query and others have Array data types. But I'm not sure which data types are supported with JMP Query Builder.  Perhaps @bryan_boone would be able to answer whether or not array data types are supported.

 

Stan

 

Re: Snowflake and Arrays

JMP doesn't support array datatypes for any SQL database.

Re: Snowflake and Arrays

@peter_polito,  Stan's route is definitely preferred as JMP works very well with the CDATA driver (both Snowflake and the driver can be obtained on a trial if you or the customer wants to check it out).  One issue you might run into with the customer is the CDATA driver for snowflake is not free.  If the customer is wanting to use the driver provided by snowflake, which is free there is a workaround not as elegant as the CDATA driver.  You will need to supply Role and Database through JSL.  Here is an example of what that code looks like, which we got in working with Globalfoundries.  The key is the last two lines.  

 

dbc = Try(
    Create Database Connection(
        "driver={SnowflakeDSIIDriver};
Server=jw10330.us-east-1.snowflakecomputing.com;
proxy= http://uswwwp1.gfoundries.com:74;
uid=xxxxxxxxxx;
pwd=yyyyyyyyy"
    ),
    -1
);

Wait( 1 );
Print( "Database Handle" );
Show( dbc );

Role_dt = Execute SQL( dbc, "USE ROLE MA_READ_ROLE;", "Set_Role" ); // you can only use databases allowed for your role
FDC_dt = Execute SQL( dbc, "USE DATABASE MA_POC", "Set Database" ); // you can only use databases allowed for your role