About JBailey

JBailey · ‎01-17-2020

Hi @karthic2612 It just so happens that I am writing an SGF paper on SAS/ACCESS Interface to Snowflake. I am using the following code to test some ideas. Keep in mind, you will need to create STAGES for some of this code to work. Check out the Snowflake DDL doc for details. The S3 example requires a valid bucket. /* Snowflake SGF Paper - Experiments - Jeff Bailey */ /* Test the various Snowflake load techniques with largish data */ /* 1 GB */ /* Generate non-trivial data from performance experiments */ /* 4.5M = 1 GB */ data work.large_table_1GB; format dt date.; do i = 1 to 4500000; dt = i + 1; mynum1 = i; text1 = "abcdefghijklmnopqrstuvwxyz0123456789"; mynum2 = i * 10; text2 = "abcdefghijklmnopqrstuvwxyz0123456789"; mynum3 = i * 10; text3 = "abcdefghijklmnopqrstuvwxyz0123456789"; mynum4 = i * 10; text4 = "abcdefghijklmnopqrstuvwxyz0123456789"; mynum5 = i * 10; text5 = "abcdefghijklmnopqrstuvwxyz0123456789"; mynum6 = i * 10; output; end; run; /* If you want to do inserts add INSERT=32000 and DBCOMMIT=0 to LIBNAME statement */ libname snow SNOW server="something.snowflakecomputing.com" db=TESTDB warehouse=TESTWH schema=THISUSER user=THISUSER pw=MyPassword123! preserve_tab_names=no; /* Get a baseline for performance */ data snow.large_table_1GB; set large_table_1GB; run; options sastrace=',,,d' sastraceloc=saslog nostsuffix; /* Load via a Snowflake internal named stage */ /* Must create an internal stage in Snowflake (via SQL) for this to work. */ data snow.large_table_1GB_named_stage (bulkload=yes bl_stage="MY_STAGE" BL_COMPRESS=yes); set large_table_1GB; run; /* Load via a Snowflake table stage */ data snow.large_table_1GB_table_stage (bulkload=yes bl_internal_stage="table/sometable" BL_COMPRESS=yes); set large_table_1GB; run; /* Load via a Snowflake user stage */ data snow.large_table_1GB_user_stage (bulkload=yes bl_internal_stage="user/someuser" BL_COMPRESS=yes); set large_table_1GB; run; /* Load via an S3 bucket */ /* Must create an S3 bucket for this to work */ /* Must ssh into the Linux machine where SAS is running and obtain new credentials - kinit, getaswkey */ data snow.large_table_1GB_s3_stage (bulkload=yes bl_bucket="snowbulk-mybucket" bl_compress=yes); set large_table_1GB; run; Good luck, Jeff

JBailey · ‎01-15-2020

Hi @Tom *** You may want to jump to the final slide image and check out the environment variable *** *** I don't believe there is a way to globally map one data type to another *** SAS cannot read the BIGINT data types, directly. There are a couple of ways to handle it. The first is to have your DBA create views that CAST the BIGINT columns to a character data type (Linus' recommendation). You can do this, without creating Teradata views, using explicit pass-thru. I have pulled some slides covering using SAS with Teradata BIGINT from a workshop that I used to deliver. Hopefully, they will help. If the slides require clarification please let me know. Be careful with TRUNCATE_BIGINT. Doing calculations using BIGINT columns could very well be problematic because you can get incorrect answers. http://support.sas.com/kb/39/831.html

JBailey · ‎12-19-2019

Hi @PGStats I have never used SAS OnDemand for Academics. SAS/ACCESS to PC Files may not be included. You can find help here - https://support.sas.com/en/software/ondemand-for-academics-support.html#get-help Best wishes, Jeff

JBailey · ‎12-17-2019

Hi @PGStats SERVER= points to the machine where SAS PC Files Server is running; your SAS admin may be able to provide this information to you. SERVER= can be a computer name (mycomp120), a full network name (mycomp120.mycompany.com), or an IP address (111.111.111). If you are connecting from UNIX SERVER= is required. On Windows clients, if you omit SERVER= it invokes AUTOSTART. PORT= specifies the port the SAS PC Files Server is listening on. The default is 9621. If SAS was configured to use a different port, you must specify PORT=. Once again, your SAS admin can tell you the values to use. Just in case you want to look at the doc: https://go.documentation.sas.com/?docsetId=acpcref&docsetTarget=n1qk7lv0f535qkn1mydookwetyrp.htm&docsetVersion=9.4&locale=en Best wishes, Jeff

JBailey · ‎11-26-2019

Hi @reprui This is a great idea. I am actually working on an article like this for Snowflake. Stay tuned! I will try to make it a habit and add more. Best wishes, Jeff

JBailey · ‎10-18-2019

To be honest, I get tired of looking for this information. Hopefully, this will help people other than me, too. Documenting the systems requirements links is a work in progress; I intend to add to it as time goes on. Feel free to request additions in the comment section. If you are interested in Hadoop, it is important to understand the following statement from the SAS Support for Alternative Releases of Hadoop Distributions : Unless otherwise noted, SAS only supports minor releases within the documented major release. The above statement only applies to Hadoop. Other databases are supported at later major releases. For information regarding SAS and Hadoop systems requirements: SAS Viya 3.5 Support for Databases SAS Viya 3.4 Support for Databases SAS 9.4 Support for Hadoop Here are the Systems Requirements for database access: SAS Viya 3.5 Support for Databases SAS Viya 3.4 Support for Databases SAS 9.4M6 Systems Requirements for Linux (Database support information lives here. You will need to find the document for your specific operating system) Configuration Information for database access: SAS Viya 3.5 (Linux) SAS Viya 3.4 (Linux) SAS 9.4M6 (Linux example. You will need to find the document for your specific operating system) If your database is on the cloud or a variant of an existing SAS supported database you will want to take a look at the SAS 9.4 Cloud and Database Variants page Keep in mind; data access documentation is in two places (SAS/ACCESS engine and SAS Data Connector): SAS/ACCESS and SAS Data Connectors. Using the previous link here is how to find the SAS/ACCESS (SAS Programming Runtime Environment - Classic SAS/ACCESS) doc: Here is how to find the SAS Data Connector doc (SAS CAS programming):

JBailey · ‎10-17-2019

Hi @Tao-Fr SAS/ACCESS Interface to Snowflake has shipped and is available. Right now, it is Linux-only. The plan is to ship on Windows soon. Here are the Systems Requirements for database access: SAS Viya 3.4 - https://support.sas.com/en/documentation/third-party-software-reference/viya/34/support-for-databases.html SAS 9.4M6 - http://support.sas.com/documentation/installcenter/en/ikfdtnlaxsr/66396/PDF/default/sreq.pdf Configuration Information for database access: SAS Viya 3.4 - https://go.documentation.sas.com/?docsetId=dplyml0phy0lax&docsetTarget=p03m8khzllmphsn17iubdbx6fjpq.htm&docsetVersion=3.4&locale=en SAS 9.4M6 - http://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/66380/PDF/default/config.pdf Keep in mind; data access documentation is in two places: SAS/ACCESS and SAS Data Connectors. Here is the doc - https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=pgmsashome&docsetTarget=home.htm&locale=en Using the previous link here is how to find the SAS/ACCESS (SAS Programming Runtime Environment - Classic SAS/ACCESS) doc: Here is how to find the SAS Data Connector doc (SAS CAS programming): I hope this helps.

JBailey · ‎10-04-2019

Hi @Gaetan My post refers to a new product that was released a few weeks ago. Best wishes, Jeff

JBailey · ‎10-04-2019

Hi @AdamT Make sure you add the INSERTBUFF= and READBUFF= options to your LIBNAME statement. Finding the "best" - if there is such a thing - setting takes experimentation. I would begin by setting each option to 10000. Best wishes, Jeff

JBailey · ‎10-02-2019

Hi @Sajid01 My pleasure. Best wishes, Jeff

JBailey · ‎10-02-2019

Hi @Sajid01 Oracle does not have schemas; it has users. In DB2, a user can issue a "CREATE SCHEMA" DDL statement. You can't do that in Oracle. It is common for Oracle DBAs to mimick schemas by creating a user, creating a bunch of objects under this user, then revoking connection privileges. In other words, it is a user who owns objects, but cannot connect to the database. In the paper, SASUSERS is actually an Oracle user. I believe that the author refers to SASUSERS as a "public schema" because it is a set of database objects used by many people. The SCHEMA=sasusers enables the SAMJI user to transparently access the tables, view, etc. that are owned by SASUSER. The author's description of PATH= is a little off, too. Here is how the author describes an Oracle Path: The data of interest reside in the path called "nesug". An Oracle Path contains the technical information (hostname, port, SID) needed to connect to an instance of Oracle. In other words, the Oracle database. I used an in-line path in the article. If you are interested in the details, take a look at it. Best wishes, Jeff

JBailey · ‎09-18-2019

Hi @Yokodaspacewale Can you include your LIBNAME statements and/or CONNECT statements because it will help us sort this out? Best wishes, Jeff

JBailey · ‎09-10-2019

Hi @Gaetan We are in the planning stages. If I had to guess, I would say the product will likely support Windows sometime first half of 2020. As a product manager, I must say, "This is a guess and not a promise." Best wishes, Jeff

JBailey · ‎08-29-2019

Hi @LMW5 The table you are creating, LOC_DEV, will be defined as a SET table (CREATE SET TABLE...). SET tables cannot contain duplicate rows. The Teradata default is to create a SET table. Multiset tables allow duplicate rows in a table (CREATE MULTISET TABLE...). Let's see if this is your issue. You can tell if your data contains duplicate rows by issuing a query similar to: -- Sample code is included below. You can try this technique on your SAS -- dataset and see if the number matches what is being INSERTed into Teradata. -- This returns 10 from my SAS dataset even though there are 20 obs in it. proc sql; select count(*) from (select distinct * from work.dups); quit; Other examples of seeing if there is a duplicate problem. Try this sample code in your environment: -- In this example I have set DBCOMMIT=1 to show the problem with a small amount -- of data. PLEASE Don't do this for your large amount of data. -- libname tera odbc dsn=tera16_DSN user=sasxjb password=mypasswd1 dbcommit=1; data work.dups; input a b $20.; cards; 1 ONE 1 oneone 2 TWO 2 twotwo 3 THREE 3 threethree 4 FOUR 4 fourfour 5 FIVE 5 fivefive 1 ONE 1 oneone 2 TWO 2 twotwo 3 THREE 3 threethree 4 FOUR 4 fourfour 5 FIVE 5 fivefive run; proc sql; create table tera.dups1 as select * from dups; quit; -- PROC append produces a cleaner log. proc append base=tera.dups2 data=dups; run; proc sql; select count(*) from tera.dups1; select count(*) from tera.dups2; run; You will see that only 10 rows are inserted into the table (there are 20 OBS in the SAS data set). I included the DBCOMMIT=1 so that the code will show the behavior. If this is your problem you will see an error message similar to this one: ERROR: CLI execute error: [Teradata][ODBC Teradata Driver][Teradata Database](-2802)Duplicate row error in sasxjb.dups1. WARNING: File deletion failed for TERA.dups1.DATA. Back to MULTISET tables - the following code should INSERT all the data into the table. proc append base=tera.dups3 (pre_table_opts='MULTISET') data=dups; run; proc sql; select count(*) from tera.dups3; run; If your data doesn't have duplicates, then there may be a space allocation problem when you create your table. Look for different error messages in the code. About FASTLOAD=. It is a great idea to use FASTLOAD when the data does not contain duplicates. If the data contains duplicates, MULTILOAD=yes is your best bet. Unfortunately, this example is using SAS/ACCESS Interface to ODBC and TPT loading capabilities aren't available. Good luck! Jeff

JBailey · ‎08-27-2019

Hi @Harsh_Vira @LinusH provides great advice regarding the OPTIONS statement. It is very important to know the query that SAS is sending to Hive. On the Hadoop side: it is important to understand how the data is arranged. Partitioning the data may help if Hive/MapReduce can use partition elimination. The Hadoop admins can help with this. It is also a good idea to try the query in HUE and see how it performs without bringing the result set into SAS. Given the number of rows in the file, I think 6 minutes may not be bad. Best wishes, Jeff

Online Status	Offline
Date Last Visited	Friday