Last time, I explored how Airflow sensors can help starting processes upon the occurrence of an event like the creation of a file or the load of new records in a database table. Today, I am going to take a look at Airflow Datasets. As explained in the Airflow documentation, Airflow Datasets are useful to make your flows “data-aware”.   In other words, Airflow Datasets allow you to trigger a DAG (Directed Acyclic Graph, a process flow in Airflow) based on the “update” of an input “Dataset”, produced by another DAG for instance. I use “quotes” because it’s all logical. A Dataset is nothing but a string that describes a logical data source or target. It is not connected to actual data, and that’s finally the beauty of it.   It’s simply declarative. For example, you can declare that a flow named MAN_THROWS “updates” a Dataset named BALL. And you can declare that a flow named DOG_RUNS is triggered when Dataset BALL is updated. You just created a dependency between MAN_THROWS and DOG_RUNS. If MAN_THROWS is triggered (for example based on a time event) and runs successfully, then Dataset BALL will be marked as updated which will trigger DOG_RUNS. It’s probably a tiny bit more subtle but you get the idea, and we will provide more examples further down.   How to declare an Airflow Dataset?   An Airflow Dataset is defined using a URI and remember that an Airflow Dataset is not connected to any data systems. It’s just a string and Airflow will not check the existence of the data it represents. So, you can be very creative about it. Let say we have a flow (DAG) whose goal is to update a fact table named ORDERS stored as a SAS data set (yes, it’s confusing, right?) in a SAS library named SALES, you could define the Airflow Dataset as:   sas://sales/orders   And that’s it. You defined an Airflow Dataset URI.   You can define as many Datasets as you want and try to match the reality of your data ecosystem:   cas://productsCaslib/product_catalog gcs://credit/transactions/operations.parquet singlestore://energy/water/water_consumption_current_year   In terms of DAG source code (Python), this will look like this:   example_dataset = Dataset("sas://sales/orders")   How to specify that a DAG updates an Airflow Dataset?   First, we need to specify what “updates” a Dataset. I was probably oversimplifying it earlier when I said that a DAG “updates” a Dataset. Actually, a task (a unit of processing belonging to a DAG) is the element that owns the update concept. In other words, a task within a DAG “produces” (or “updates”) a Dataset. A task can update multiple Datasets. A DAG can contain multiple tasks that update a Dataset.   So, the following flow is NOT what really happens:     Instead, the following one represents more accurately the actual workflow:     Bottom line: a consumer DAG that depends on Datasets – here DAG 3 – may start before the producer DAGs finish – here DAG 1 and DAG 2. Indeed, as soon as the tasks that update both Datasets are finished and successful, DAG 3 can start. This can be useful to have that level of granularity.   Having said that, let’s see a code example of how to specify that a task updates a Dataset:   task1 = SASStudioOperator(task_id="1_LoadOrders.flw", exec_type="flow", path_type="content", path="/Public/Load Orders.flw", compute_context="SAS Studio compute context", connection_name="sas_default", exec_log=True, codegen_init_code=False, codegen_wrap_code=False, outlets=[Dataset("sas://sales/orders")], trigger_rule='all_success', dag=dag)   This is done through the outlet parameter (standard option of the main operator class). If this task (among other tasks in a DAG) runs successfully, the Dataset sas://sales/orders will be marked as “updated”. You can specify multiple Datasets in this outlet parameter.     How to specify that a DAG is triggered by an Airflow Dataset update?   Now, how do we tell a DAG that it should start when a Dataset has been marked “updated”? Simply by using a special string in the schedule parameter.   The schedule parameter can take a cron expression, a cron preset, a timedelta expression or a Dataset expression:   dag = DAG(dag_id="Prepare_Data_for_Analytics", schedule=[Dataset("sas://sales/orders")], start_date=datetime(2024,3,14), catchup=False)   This DAG will start as soon as the Dataset sas://sales/orders is being updated. And it will start every time the Dataset is being updated.     If you look at the DAGs list in Airflow, you will observe this:     Get the full code of the two sample DAGS here.     The cherry on the cake: Datasets lineage   The Airflow user interface provides a lineage view (“Datasets” menu) of the relationships between DAGs and Datasets. This is super helpful to understand your orchestration sequence.     As a conclusion, I will just mention that I updated my “Airflow – Generate DAG” custom steps on GitHub to include the ability to define dependencies between your DAGs using Airflow datasets:     Thanks for reading.     Find more articles from SAS Global Enablement and Learning here. ... View more

Since the release of the SAS Airflow Provider, some people at SAS have been exploring how Airflow can help orchestrate and schedule your SAS data pipelines, and trying to figure out how to map common scheduling capabilities to Airflow concepts.   Among them is the ability to start a process (a DAG in the context of Airflow) upon the occurrence of an event (other than just a specific time event). Typical events of interest are the arrival of a data file, the presence of specific records in a database table, the success of an external task, etc.   This is where Airflow Sensors come into play. “Sensors are a special type of Operator that are designed to do exactly one thing - wait for something to occur.”   There is already a lot of very good literature on this topic, and I won’t even try to explain them more. This article is very nice. My intent here, is just to give some examples and considerations.   I hear it coming… “be careful with sensors”, “sensors taking up a full worker slot for the entire time they are running”, “the Sensor deadlock issue”, “you should use deferrable operators”, etc.   All right, this makes sense.   But before optimizing fairly complicated systems, let’s try to solve basic scheduling challenges. And Airflow Sensors are very simple to understand and to use even for people not really proficient in Python like me. The opposite of deferrable operators.   Anyway, let’s just give an example. You want to run a SAS flow every day that ingests a data file from disk, and you want to start it as soon as the file is created. You will use a File Sensor.   A sensor is nothing less than a specific operator. Thus, it shows up as a task when you define it in a DAG:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   In the graph representation above, you can see a FileSensor task on the left, which when it will be satisfied, will trigger the SASStudioOperator task on the right.   What does the FileSensor code look like? Pretty simple:   task1 = FileSensor(task_id="1_wait_for_file", filepath="/gelcontent/data/contact_list2.csv", fs_conn_id="fs_default", poke_interval=60, timeout=3600, mode="reschedule", dag=dag)   filepath defines the file whose existence is to check. fs_conn_id defines the file system (where to check the file) connection id defined globally in Airflow. poke_interval defines the number of seconds to wait before checking again. timeout defines the number of seconds to wait before the task times out in case the file does not arrive (the FileSensor task will then have a FAILED status by default). mode defines if the task runs continuously while doing its file check (“poke”), using a worker slot all the time, or if the task stops after each check and restarts before each check (“reschedule”), freeing up a worker slot between 2 checks. Now, where is the file you are checking?   Generally, the file will be needed by the subsequent task, in our case, SAS. So, it has to be accessible from SAS.   However, it will be checked by Airflow, which, depending on how it is deployed (on bare OS, as containers or in Kubernetes), has its own file access setup.   By default, SAS Viya and Airflow won’t have access to the same resources out of the box.   You will need to make Airflow and SAS sharing access to the same file system.   In my case, where both SAS Viya and Airflow are deployed in the same Kubernetes cluster, it is quite easy to set up an identical access to the same NFS. Both applications will share the same “view”.   If, instead, you want to wait for a file on a Cloud Object Storage, then it is easier because you don’t have to deal with local file access. Accessing cloud object resources is quite universal. Sensors do exist for the main cloud object storage providers (AWS S3, GCS, Azure Blob Storage (not sure about ADLS)) and they do seem to provide a “deferrable” version of them (cf. the Sensor deadlock issue!).   Checking a database table for some records before running a SAS job is also something possible with an Airflow Sensor. Let’s see an example of a SqlSensor syntax:   task1 = SqlSensor( task_id='waiting_for_data', conn_id='postgres', sql="select * from load_status where data_scope='DWH' and load_date='{{ ds }}'", poke_interval=30, timeout=60 * 5, mode='reschedule', dag=dag )   Here, we specify the database connection we want to use (defined globally) and the SQL code we want to run.   The default behavior (which can be customized of course) is the following: if the query does not return any row, the condition is not satisfied, and the sensor will then try it again later. if the query returns rows, the condition is satisfied, the sensor gets a SUCCESS status, and all linked downstream tasks are run.   So, you need to write your SQL query in a way that fits your target condition. The query can use macro-variables provided by Airflow. Here {{ ds }} represents the DAG run date.   As a conclusion, I will just mention that I updated my “Airflow – Generate DAG” custom steps on GitHub to include the ability to define a FileSensor (thanks to Lorenzo Toja for this idea):     Next time, I will talk about another Airflow concept that I also included in my tools: Airflow Datasets to make your flows “data-aware”.   Thanks for reading. ... View more

SAS Viya with SingleStore is a solution that embeds a SingleStore database in SAS’ analytics platform. The combined power of SAS Viya and SingleStore enables customers to take advantage of SAS analytics on data stored in a modern high-performance database, avoiding data duplication, minimizing data movement and reducing hardware needs. SAS Viya with SingleStore has been recently complemented with scoring acceleration, making the entire analytics lifecycle inside SingleStore a reality.   Indeed, you can: Prepare data in SingleStore (ETL/ELT capabilities in SAS Studio, pass-through, etc.) Build predictive models on SingleStore data (data streaming and caching, multi-pass analytics, etc.) Publish and run models directly in SingleStore (scoring acceleration) In this article, I will focus SingleStore scoring acceleration which has been released in May 2023 and thus is available in LTS 2023.10.   Lifecycle   This over-simplified lifecycle below illustrates what usually needs to be done to reach the goal which is scoring database data in place without moving the data.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.       Designing the model is probably the most important and time-consuming phase (and I’m not even talking about the data preparation beforehand that takes a lot of time).   The two remaining steps (publish and run) are simple technical steps. With SingleStore embedded in SAS Viya’s platform, the process is smooth and requires no additional configuration.   Design the model   Here, I will just show a code example of a gradient boosting model as well as the caslib definition. Gradient boosting requires multiple passes of the data. I set the multipassMemory option to “cache” in order to cache temporarily the data set in CAS for better performance.     /* SingleStore caslib */ caslib s2 dataSource=(srctype='singlestore', database="&MYS2DB", epdatabase="&MYS2DBWORK", host="&MYS2HOST", pass="&MYS2PWD", port=&MYS2PORT, user="&MYS2UID", multipassMemory="cache" ) libref=cass2 ; /* Load the SingleStore training table in CAS - no duplication */ proc casutil incaslib="s2" outcaslib="s2" ; dropTable casdata="hmeq_train" quiet ; load casdata="hmeq_train" casout="hmeq_train" ; quit ; /* Gradient Boosting modeling */ proc gradboost data=cass2.hmeq_train seed=12345 ; id record_pk ; input Delinq Derog Job nInq Reason / level = nominal ; input CLAge CLNo DebtInc Loan Mortdue Value YoJ / level = interval ; target Bad / level = nominal ; /* Save an analytic store */ savestate rstore=cass2.gradboost_store ; run ;   What is interesting with SAS Viya with SingleStore is that this model is designed directly on SingleStore data (which is filtered in SingleStore, streamed in real-time from SingleStore and cached in CAS for the duration of the process). The input table does not need to be permanently duplicated and copied over to CAS before. See SAS Viya with SingleStore: Data Flow Concept.   Notice the output of the modeling phase: an ASTORE (analytic store) stored in a CAS table that we will use later. Other model types are supported too.   Publish the model   If you have SAS Viya with SingleStore, everything is already configured. Publishing a model just consists in running the following piece of code:   /* Publish the model */ proc scoreaccel sessref=mysession ; publishmodel target=singlestore caslib="s2" modelname="GradientBoosting_Code" modeltype=ds2 storetables="s2.gradboost_store" modelnotes="Simple gradient boosting test model" keeplist=yes replacemodel=yes ; quit ; Here, we want to publish the model currently stored in the gradboost_store CAS table, created during the design phase.   As a result, a SingleStore table, named sasmodel.GradientBoosting_Code, is created and contains the SAS model:     Run the model   This is the final step. Now that the SAS model is in SingleStore, we will be able to score SingleStore data without having to move the data elsewhere. This is done with the following code:   /* Run the model in SingleStore */ /* No Data Movement */ proc scoreaccel sessref=mysession ; runmodel target=singlestore caslib="s2" modelname="GradientBoosting_Code" intable="hmeq_prod" outtable="hmeq_prod_code_scored" outkey="RECORD_PK" verbose ; quit ;   We specify the model we want to use, the input table to read, the output table to create and the key we want to use (the output table will only contain the key plus the model output variables). This uses the SAS Embedded Process (deployed automatically in SingleStore) behind the scenes.   The scoring output table will look like this:     Iterate again   Of course, this lifecycle is based on iterations. A model can perform well one day but might be less relevant one month later. And you might want to publish and run new models.   You can also manage your models in SingleStore and remove old ones:   /* Delete a model from SingleStore database */ proc scoreaccel sessref=mysession ; deletemodel target=singlestore caslib="s2" modelname="GradientBoosting_Code" ; quit ;  Scoring acceleration is an important step in reducing data movement between SingleStore and SAS Viya. This is a very relevant capability in SAS Viya with SingleStore to keep maximizing benefits from a high-performance database with enterprise-class analytics.   Thanks for reading. ... View more

I’ve been waiting for this option for years! It is now possible to specify the number of replications (or copies) at a (more) global level. Yay!   Indeed, until now, by default, tables were loaded in CAS with an immutable default “copies” of 1 (allowing to survive 1 CAS node failure). Or you could customize the number of copies using code for each table load. There was no way to globally set a different value for copies.   Who has never coded this?   proc casutil incaslib="dm" outcaslib="dm" ; load casdata="bigprdsale.parquet" casout="bigprdsale" replace copies=3 ; quit ; data casdm.class(copies=0) ; set sashelp.class ; run ; proc cas ; action table.loadTable / path='film', caslib='dm_pgdvd', casOut={name='film', caslib='dm_pgdvd', replace=true, replication=2}; quit ;   PS: COPIES and REPLICATION are synonyms. However, they cannot be used interchangeably.   Generally, SAS users/administrators want to:   Use a value greater than 1 for large CAS clusters. Indeed, if you work with a 20-nodes CAS cluster, you may want to survive more than 1 CAS node failure. Use a value of 0 copies to optimize your SAS ETL processes when you don’t really care about high availability. And very often, you don’t care about high availability of your numerous intermediate CAS tables that you create during a data flow.   Setting COPIES to 0 is always a good optimization practice for complex SAS code that processes data in CAS. You may just want the final table (a datamart table for instance) to be highly available, but not all the tables involved in the process.   The more copies you set, the slower it is to create/load data in CAS, the more CAS_DISK_CACHE space you need.   So, what is possible now in SAS Viya 2023.09?   In SAS code, when you start a new CAS session, you can set a default table replication factor that will be used on all tables you will load/create using the session.   In the following code, I’m setting a CAS session option – defaulttablereplication – to 2 at the beginning of my CAS session. All new tables created in that session, session-scoped or global, will be created with that new default of 2 copies.   cas mysession sessopts=(defaulttablereplication=2) ; %put defaulttablereplication: %sysfunc(getsessopt(mysession, defaulttablereplication)) ; proc casutil incaslib="dm" outcaslib="dm" ; list files ; load casdata="prdsale.sas7bdat" casout="prdsale" replace ; load casdata="bigprdsale.parquet" casout="bigprdsale" replace ; load incaslib="dm_pgdvd" outcaslib="dm_pgdvd" casdata="film" casout="film" replace ; list tables ; quit ;   You can still override the default session value with a specific value in your loading step if you want:   cas mysession sessopts=(defaulttablereplication=2) ; %put defaulttablereplication: %sysfunc(getsessopt(mysession, defaulttablereplication)) ; proc casutil incaslib="dm" outcaslib="dm" ; list files ; load casdata="prdsale.sas7bdat" casout="prdsale" replace ; load casdata="bigprdsale.parquet" casout="bigprdsale" replace copies=0 ; load incaslib="dm_pgdvd" outcaslib="dm_pgdvd" casdata="film" casout="film" replace copies=1 ; list tables ; quit ; libname casdm cas caslib="dm" ; data casdm.class(copies=0) ; set sashelp.class ; run ;   If you have a long and complex SAS process based on the CAS engine, and you want it as fast as it can be, you can easily set the default copies to 0:   cas mysession sessopts=(defaulttablereplication=0) ; %put defaulttablereplication: %sysfunc(getsessopt(mysession, defaulttablereplication)) ; /* Load source tables */ proc casutil incaslib="dm" outcaslib="dm" ; list files ; load casdata="prdsale.sas7bdat" casout="prdsale" replace ; load casdata="bigprdsale.parquet" casout="bigprdsale" replace ; load incaslib="dm_pgdvd" outcaslib="dm_pgdvd" casdata="film" casout="film" replace ; list tables ; quit ; libname casdm cas caslib="dm" ; /* Data manipulations */ data casdm.class_def ; set sashelp.class ; run ; /* many ETL steps */ /* ... */ /* many ETL steps */ /* ... */ /* Make the final table highly available (?) */ data casdm.final_table(copies=3 promote=yes) ; set casdm.class_def ; run ; cas mysession terminate ;   What about tables loaded in the UI (Manage Data)?   They are still loaded with the immutable default “copies” of 1 (allowing to survive 1 CAS node failure). SAS is working on providing an equivalent option at the CAS server level. I'll update this article when it is available. In the meantime, if you want to load tables with a different value for copies or replication, you will have to load them using code.   Thanks for reading.     Find more articles from SAS Global Enablement and Learning here. ... View more

Apache Airflow is a popular platform for designing, scheduling, and monitoring process flows. SAS integrates nicely with Airflow through the SAS Airflow Provider, a package that allows SAS users to run SAS assets from Airflow. We have already presented how SAS works with Airflow here and here. Since the first release of this provider, earlier this year, very useful functionalities have been added (latest version addressed by this blog is 0.0.7). Let’s review them in this post.   A new SAS Studio operator   In order to provide the following feature below (run a SAS program from Airflow), it was decided to create a new operator that supersedes SASStudioFlowOperator. Indeed, the new SASStudioOperator operator (note the name change to suggest it’s no longer dedicated to running flows) is the operator to use from now on for flows and programs. The old one will still work to execute flows, but any new feature will be added only to SASStudioOperator.   Ability to run a SAS program (in addition to SAS Studio flows and SAS job definitions)   This one is great! SASStudioOperator can execute SAS programs stored in SAS content or the SAS Compute file system. You no longer need to copy your code into a flow or create a job definition from a program. You can now directly reference your SAS program in an Airflow DAG through the new SASStudioOperator class.   In the following example, the Airflow task defines the execution of the /Public/Programs/code1.sas program available in SAS content. Note the new exec_type="program" option value.     task1 = SASStudioOperator(task_id="1_code1.sas", path_type="content", exec_type="program", path="/Public/Programs/code1.sas", compute_context="SAS Studio compute context", connection_name="sas_default", exec_log=True, codegen_init_code=False, codegen_wrap_code=False, macro_vars=global_params, env_vars=global_params, dag=dag)   Ability to embed SAS code as part of the Airflow DAG   On the same topic, one can embed the SAS code to run in the DAG definition. This could be useful for quick testing purposes.    program1 = ''' %let TEST_var1=john ; %let TEST_var2=jack ; %let DEV_var3=jill ; ''' task1 = SASStudioOperator(task_id="1_code1.sas", path_type="raw", exec_type="program", path=program1, compute_context="SAS Studio compute context", connection_name="sas_default", exec_log=True, codegen_init_code=False, codegen_wrap_code=False, macro_vars=global_params, env_vars=global_params, dag=dag)   Note the new path_type="raw" option value and the path= option pointing to the Python object containing the SAS code to run.   Ability to pass macro-variables to SAS Studio flows and SAS programs   It was already possible to send parameters to a flow as environment variables which can be easily resolved in SAS (%sysget(myvar)). Even easier is now the ability to send parameters as SAS macro-variables.   With the following task definition (note the new macro_vars= option):   global_params = { "VAR1": "val1", "VAR2": "val2" } program1 = ''' %let TEST_var1=john ; %let TEST_var2=jack ; %let DEV_var3=jill ; ''' task1 = SASStudioOperator(task_id="1_code1.sas", path_type="raw", exec_type="program", path=program1, compute_context="SAS Studio compute context", connection_name="sas_default", exec_log=True, codegen_init_code=False, codegen_wrap_code=False, macro_vars=global_params, dag=dag)   You will get this in the SAS log (visible from Airflow):   [2023-08-04, 18:39:21 UTC] {sas_studio.py:197} INFO - Adding 2 macro variables to code ... [2023-08-04, 18:39:34 UTC] {logging_mixin.py:150} INFO - 10 /** Begin macro variables **/ [2023-08-04, 18:39:34 UTC] {logging_mixin.py:150} INFO - 11 %LET VAR1 = val1; [2023-08-04, 18:39:34 UTC] {logging_mixin.py:150} INFO - 12 %LET VAR2 = val2; [2023-08-04, 18:39:34 UTC] {logging_mixin.py:150} INFO - 13 /** End macro variables **/   Ability to extract macro-variables from SAS Studio flows and SAS programs   It is also possible to extract SAS macro-variables from a task so that they can be sent to downstream tasks. To achieve that, SAS macro-variables whose name starts with output_macro_var_prefix option value will be pushed to an XCom, an Airflow mechanism that allows you to hold values.   Then you will be able to reuse those variables by pulling them from the XCom and send them to a downstream task.   Here is an example of pushing macro-variables to an XCom. Note the output_macro_var_prefix="TEST_" option. Only two macro-variables will be pushed in this example.   program1 = ''' %let TEST_var1=john ; %let TEST_var2=jack ; %let DEV_var3=jill ; ''' task1 = SASStudioOperator(task_id="1_code1.sas", path_type="raw", exec_type="program", path=program1, compute_session_id="{{ ti.xcom_pull(key='compute_session_id', task_ids=['create_sess'])|first }}", compute_context="SAS Studio compute context", connection_name="sas_default", exec_log=True, codegen_init_code=False, codegen_wrap_code=False, macro_vars=global_params, output_macro_var_prefix="TEST_", dag=dag)   In a downstream task, you can pull these variables individually and inject them in the macros_vars parameter:   program6 = ''' %put &=TEST_var1 ; %put &=TEST_var2 ; %put &=DEV_var3 ; %put _all_ ; %put &=systime ; ''' task6 = SASStudioOperator(task_id="6_code4.sas", path_type="raw", exec_type="program", path=program6, compute_context="SAS Studio compute context", connection_name="sas_default", exec_log=True, codegen_init_code=False, codegen_wrap_code=False, macro_vars={"TEST_var1": "{{ti.xcom_pull(key='TEST_VAR1', task_ids=['1_code1.sas'])|first}}", "TEST_var2": "{{ti.xcom_pull(key='TEST_VAR2', task_ids=['1_code1.sas'])|first}}"}, dag=dag)   In this particular case, only two macro-variables will resolve. DEV_var3 will not.   Note the syntax to pull values from a specific task’s XCom:   {{ti.xcom_pull(key='TEST_VAR1', task_ids=['1_code1.sas'])|first}}   Ability to pass Airflow context variables to SAS Studio flows / SAS programs / SAS job definitions   Airflow provides some interesting contextual environment variables. For example:   AIRFLOW_CTX_DAG_OWNER='airflow' AIRFLOW_CTX_DAG_ID='aaa_test_5_raw' AIRFLOW_CTX_TASK_ID='1_code1.sas' AIRFLOW_CTX_EXECUTION_DATE='2023-08-04T19:00:56.255394+00:00' AIRFLOW_CTX_TRY_NUMBER='1' AIRFLOW_CTX_DAG_RUN_ID='manual__2023-08-04T19:00:56.255394+00:00'   They are now passed by default to a SAS Studio flow or SAS program as environment variables (SASStudioOperator). You can get them using %sysget(AIRFLOW_CTX_DAG_ID) for example.   They are now passed optionally to a SAS job definition as SAS macro-variables (SASJobExecutionOperator). The add_airflow_vars option controls this behavior.   task4 = SASJobExecutionOperator(task_id="4_job12", job_name="/Public/Jobs/job12", connection_name="sas_default", job_exec_log=True, add_airflow_vars=True, parameters=global_params, dag=dag)   A new operator to create Compute sessions   Previously, each task was creating its own SAS Compute session. Now, you can have a finer-grained control by creating yourself the SAS Compute sessions needed, using a new operator named SASComputeCreateSession.   task0 = SASComputeCreateSession(task_id="create_sess", dag=dag)   Ability to reuse Compute sessions   One of the benefits of the previous feature is the ability to reuse existing Compute sessions. This can be helpful:   From a performance perspective: you can minimize the latency of obtaining a new Compute session From a context perspective: you can leverage the state of an existing session (options, macro-variables, tables in the WORK, etc.) Reusing Compute sessions makes a lot of sense when you have a sequence of tasks that you want to run in serial. Each task runs after the previous one and thus will use the same compute session. This does not fit well for process flows that run tasks in parallel. In this case, you want to keep parallel execution by maintaining different Compute sessions (and not reusing them).   How to reuse a Compute session?   Once you have started a new Compute session using the new SASComputeCreateSession operator (see syntax example in previous feature), the session id is stored in the task’s XCom.     You can make subsequent tasks reuse that session by specifying this existing session id in the compute_session_id option:   task1 = SASStudioOperator(task_id="1_code1.sas", path_type="raw", exec_type="program", path=program1, compute_session_id="{{ ti.xcom_pull(key='compute_session_id', task_ids=['create_sess'])|first }}", exec_log=True, dag=dag)   The following Python syntax allows you to extract a value from the create_sess task’s XCom:   {{ ti.xcom_pull(key='compute_session_id', task_ids=['create_sess'])|first }}   Ability to get the SAS log from a job definition execution   Getting the SAS log in Airflow was already possible for SAS Studio flows but was missing for SAS job definitions (SASJobExecutionOperator). To view the SAS log, we had to go to SAS Environment Manager > Job and Flows > Monitoring.   Now, the job_exec_log option has been implemented in the SAS Job Execution operator. This is very useful to be able to view all logs from different tasks (flows, programs and jobs) in the same Airflow UI.   task3 = SASJobExecutionOperator(task_id="3_job11", job_name="/Public/Jobs/job11", connection_name="sas_default", job_exec_log=True, add_airflow_vars=False, parameters=global_params, dag=dag)     All of these new capabilities make SAS integration with Airflow more efficient and seamless. This is a great complement to the SAS Viya platform.   Thanks to Andrew Shakinovsky for his help on this topic.   Thanks for reading.     Find more articles from SAS Global Enablement and Learning here. ... View more

While SAS/ACCESS engines and Data Connectors are included in every SAS Viya foundational offering, there is still work to do to configure access to databases from SAS Viya. If the configuration of SAS/ACCESS is part of your role, you probably know that most of the information necessary is available in the README(s) file(s). You can access it in the my.sas.com portal or in the deployment assets once you have downloaded and extracted them.   In this blog, I will walk through the SAS/ACCESS configuration section of the README file.   Why do I need to “attach storage to SAS Viya” in the context of SAS/ACCESS?   SAS/ACCESS (in its entirety, which includes Data Connectors) relies on a piece of software (aka the “database client” or sometimes simply a database/ODBC/JDBC driver) accessible locally to communicate with a database server available mostly remotely. Some database clients are already embedded in a SAS Viya deployment like PostgreSQL, Microsoft SQL Server, Snowflake, etc. But several others are not.   And this is where the attached storage comes into play. You need a place to install database clients and make them available to all SAS Viya components that need them. Usually, you can use NFS to share them easily and with SAS Viya you can provision a NFS server using the Terraform scripts provided (SAS Viya Infrastructure As Code GitHub projects: viya4-iac-azure, viya4-iac-gcp, viya4-iac-aws).   That drives us to some other questions.   Which SAS components need access to a database client? How do I attach storage to those components?   SAS Viya has many data processing engines: SAS Compute Server, CAS, MAS, ESP, etc. They potentially need access to database data. Configuring access to database clients in the Kubernetes world is not a trivial task.   SAS provides example files (PatchTransformers) to attach the storage you used to install the database clients so that it is accessible from the required pods.   In the recent versions (since release 2023.03), 4 files are provided and their names don’t make it easy to understand which SAS engine is targeted (except maybe the first one).   You can find them in $deploy/sas-bases/examples/data-access:   data-mounts-cas.sample.yaml Use it when you want to make the database clients installed available to CAS (controller and workers). The component targeted by this PatchTranformer is sas-cas-operator.   data-mounts-deployment.sample.yaml Use it when you want to make the database clients installed available to SAS/CONNECT. The component targeted by this PatchTranformer is sas-connect-spawner.   data-mounts-job.sample.yaml Use it when you want to make the database clients installed available to SAS Compute Sessions triggered in different contexts. The components targeted by this PatchTranformer are sas-batch, sas-compute, sas-connect, sas-job-flow-scheduling and sas-launcher.   data-mounts-statefulset.sample.yaml Use it when you want to make the database clients installed available to SAS Cloud Data Exchange (colocated data agent). The component targeted by this PatchTranformer is sas-data-agent-server-colocated.   Anatomy of a SAS/ACCESS PatchTransformer   Let’s have a look at the data-mounts-cas.sample.yaml (for CAS) sample PatchTransformer file and identify the main sections:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Proposed SAS/ACCESS Configuration Procedure   Finally, when we have all the information in mind, we can summarize SAS/ACCESS configuration with the following to-do list. We assume here that we are deploying SAS Viya from the “Client” machine (the machine where we downloaded SAS Viya assets). Identify the Jump Server VM (bastion host) and the NFS Server VM provisioned for SAS Viya (the NFS Server /export directory is by default mounted on the Jump Server as /viya-share). Prepare some directories on the NFS Server through the Jump Server. For example: /viya-share/access-clients (root directory) /viya-share/access-clients/odbc (to store odbc.ini and odbcinst.ini) /viya-share/access-clients/jdbc (to store additional JDBC drivers) /viya-share/access-clients/oracle (to store the Oracle client for example) /viya-share/access-clients/teradata (to store Teradata client for example) Install needed database clients into their corresponding folders (/viya-share/access-clients/<database>). Copy/paste needed JDBC drivers to /viya-share/access-clients/jdbc. Copy/paste sample odbc.ini and odbcinst.ini from $deploy/sas-bases/examples/data-access (odbc.sample.ini and odbcinst.sample.ini) on the Client machine to /viya-share/access-clients/odbc on the Jump Server and remove “.sample” from the file names. Configure ODBC data sources if needed in odbc.ini and odbcinst.ini. Create $deploy/site-config/data-access on the Client machine. Copy PatchTransformer’s YAML files $deploy/sas-bases/examples/data-access/data-mounts-*.sample.yaml to $deploy/site-config/data-access and remove “.sample” from the file names. Edit them accordingly (you can edit only the ones needed, for example CAS, or all 4 files so that all SAS components can access database clients). I suggest the latter. data-mounts-cas.yaml example:   # General example for adding mounts to CAS workers # PatchTransformer apiVersion: builtin kind: PatchTransformer metadata: name: data-mounts-cas patch: |- ## NFS path example - kubernetes will mount these for you - op: add path: /spec/controllerTemplate/spec/containers/0/volumeMounts/- value: name: access mountPath: "/access-clients" - op: add path: /spec/controllerTemplate/spec/volumes/- value: name: access nfs: path: /export/access-clients server: ## JAR files for JDBC NFS path example - kubernetes will mount these for you ## Do not change the mountPath value from "/data-drivers/jdbc" - op: add path: /spec/controllerTemplate/spec/containers/0/volumeMounts/- value: name: jdbcjars mountPath: "/data-drivers/jdbc" - op: add path: /spec/controllerTemplate/spec/volumes/- value: name: jdbcjars nfs: path: /export/access-clients/jdbc server: target: kind: CASDeployment annotationSelector: sas.com/sas-access-config=true Copy environment variables definition file $deploy/sas-bases/examples/data-access/sas-access.properties to $deploy/site-config/data-access. Edit it accordingly to define the needed environment variables. This will use the “mountPath” information defined in the PatchTransformers. For example: ODBCINI=/access-clients/odbc/odbc.ini ODBCINST=/access-clients/odbc/odbcinst.ini ORACLE=/access-clients/oracle/instantclient_21_10 ORACLE_HOME=/access-clients/oracle/instantclient_21_10 ORACLE_BIN=/access-clients/oracle/instantclient_21_10 COPLIB=/access-clients/teradata TERADATA=/access-clients/teradata/client/17.20/lib64 Edit your base kustomization.yaml in $deploy to include the PatchTransformers (data-mounts-*.yaml) created earlier (use the transformers block). Edit your base kustomization.yaml in $deploy to include the environment variables file (sas-access.properties) created earlier (use the configMapGenerator block) Edit your base kustomization.yaml in $deploy to include the provided data-env.yaml file (use the transformers block) Deploy/redeploy SAS Viya Restart CAS if needed Since we use the reserved "/data-drivers/jdbc” path as the mount path for JDBC drivers, they will be auto-discovered. No need to change the CLASSPATH in the JDBC libraries and CASLIBs. See this article.   Thanks for reading.   Find more articles from SAS Global Enablement and Learning here. ... View more

SAS has released SAS Airflow Provider earlier this year. This tool allows SAS administrators/power users to orchestrate SAS jobs and SAS Studio flows using Apache Airflow, an “open-source platform for developing, scheduling, and monitoring batch-oriented workflows”.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Apache Airflow can be deployed in many ways and might already exist at your site. If not, then it might be the opportunity to think it twice and make SAS Viya and Apache Airflow work hand in hand. In this blog, we will look at some aspects of Apache Airflow deployment that can help you leverage a seamless integration between SAS Viya and Apache Airflow.     Generalities   Apache Airflow and Kubernetes   While it is not mandatory for Airflow to work with SAS Viya, Airflow can be deployed in Kubernetes which makes it flexible, cloud-native and elastic. An Official Helm Chart for Apache Airflow is available to deploy it in Kubernetes very easily.   Airflow can be deployed in the same Kubernetes cluster as SAS Viya, leveraging the same infrastructure, in a different namespace for software isolation.   If dedicated to SAS Viya, Airflow workload should only consist into calls of SAS jobs and SAS Studio flows which in turn run in the SAS Viya platform.   Finally, to be able to use the SAS operators in Airflow, the default container image used in the Airflow Helm chart needs to be extended to include SAS Airflow Provider.     Apache Airflow and SAS   First, let’s remind that what we would probably call a process flow (a collection of tasks/programs/jobs organized in a specific sequence) is called a DAG (Directed Acyclic Graph) in Airflow and is defined in a Python script. So just code. No authoring UI is available out of the box.     Also, Airflow DAGs (Python scripts) are automatically discovered by the Airflow framework when they are saved in a determined DAGs directory. That said, if Airflow and SAS Viya share the same DAGs directory, it is possible to define an Airflow DAG from within SAS Viya that is automatically discovered in Airflow facilitating the integration.       In details   Step 1 – Extending the standard image   First step is to extend the default Airflow container image to include the pieces that will allow us to call SAS Viya jobs or flows. Indeed, Airflow comes out of the box with several providers (also called operators) which makes integration with third-party tools possible (essentially Airflow triggers jobs from various applications). Numerous providers are not included by default and need to be installed.   Below is an example of how to extend the default container image. Here, we use a Dockerfile with a requirements.txt file to build a new image that will include the SAS Airflow provider.   tee ./requirements.txt > /dev/null << EOF sas-airflow-provider EOF tee ./Dockerfile > /dev/null << EOF FROM apache/airflow:latest RUN pip install --upgrade pip COPY requirements.txt . RUN pip install -r requirements.txt EOF docker build -t airflow-sas:1.0.0 .   Then you have to make this image available for your deployment (multiple options described in point 4 here). It can be done by pushing it to a remote registry:   docker tag airflow-sas:1.0.0 registry.example.com/project/airflow/airflow-sas:1.0.0 docker push registry.example.com/project/airflow/airflow-sas:1.0.0     Step 2 – Deploy Apache Airflow   We are ready to start the process of deploying. First thing is to customize the Airflow deployment. There are several properties that we want to modify. You can have a look at all the chart’s properties here or here or you can write them down in a file to help you get started:   helm repo add apache-airflow https://airflow.apache.org helm show values apache-airflow/airflow > values.yaml   We are going to focus on a few properties of interest, but I will provide the values.yaml file I use at the end:   defaultAirflowRepository: registry.example.com/project/airflow/airflow-sas defaultAirflowTag: "1.0.0"   This is the repository address in your registry where you saved your customized container image. You also need to specify the saved tag name.   ingress.web.hosts: [airflow.example.com] ingress.web.ingressClassName: "nginx"   You define the Ingress host (essentially the Airflow UI URL) and you can reuse the Ingress class name of your SAS Viya deployment.   extraEnv: | - name: AIRFLOW__CORE__LOAD_EXAMPLES value: 'True' - name: AIRFLOW__SCHEDULER__DAG_DIR_LIST_INTERVAL value: '30'   You can add some environment variables. Here I want to get some sample DAGs already loaded and I want to reduce the scanning time of the DAGs directory – how often (in seconds) to scan the DAGs directory for new files – from 5 minutes to 30 seconds (demo settings to quickly discover a DAG in Airflow when a file is dropped in the DAGs directory).   volumes: - name: dags nfs: server: nfs.example.com path: /shared/gelcontent/airflow/dags volumeMounts: - mountPath: '/opt/airflow/dags' name: 'dags'   Finally, I want to customize the default DAGs directory (the main directory that Airflow scans to detect new DAGs) to be a location on a NFS server shared between Airflow and SAS Viya. Indeed, that will be handy when I want to author a DAG from within SAS. I will save it to the DAGs directory, and it will automagically appear in Airflow.   Here is the complete values.yaml file I use:   # Default airflow repository -- overridden by all the specific images below defaultAirflowRepository: registry.example.com/project/airflow/airflow-sas # Default airflow tag to deploy defaultAirflowTag: "1.0.0" # Ingress configuration ingress: # Enable all ingress resources (deprecated - use ingress.web.enabled and ingress.flower.enabled) enabled: ~ # Configs for the Ingress of the web Service web: # Enable web ingress resource enabled: true # Annotations for the web Ingress annotations: {} # The path for the web Ingress path: "/" # The pathType for the above path (used only with Kubernetes v1.19 and above) pathType: "ImplementationSpecific" # The hostname for the web Ingress (Deprecated - renamed to ingress.web.hosts) host: "" # The hostnames or hosts configuration for the web Ingress hosts: [airflow.example.com] # - name: "" # # configs for web Ingress TLS # tls: # # Enable TLS termination for the web Ingress # enabled: false # # the name of a pre-created Secret containing a TLS private key and certificate # secretName: "" # The Ingress Class for the web Ingress (used only with Kubernetes v1.19 and above) ingressClassName: "nginx" # configs for web Ingress TLS (Deprecated - renamed to ingress.web.hosts[*].tls) tls: # Enable TLS termination for the web Ingress enabled: false # the name of a pre-created Secret containing a TLS private key and certificate secretName: "" # HTTP paths to add to the web Ingress before the default path precedingPaths: [] # Http paths to add to the web Ingress after the default path succeedingPaths: [] # Configs for the Ingress of the flower Service flower: # Enable web ingress resource enabled: false # Annotations for the flower Ingress annotations: {} # The path for the flower Ingress path: "/" # The pathType for the above path (used only with Kubernetes v1.19 and above) pathType: "ImplementationSpecific" # The hostname for the flower Ingress (Deprecated - renamed to ingress.flower.hosts) host: "" # The hostnames or hosts configuration for the flower Ingress hosts: [] # - name: "" # tls: # # Enable TLS termination for the flower Ingress # enabled: false # # the name of a pre-created Secret containing a TLS private key and certificate # secretName: "" # The Ingress Class for the flower Ingress (used only with Kubernetes v1.19 and above) ingressClassName: "" # configs for flower Ingress TLS (Deprecated - renamed to ingress.flower.hosts[*].tls) tls: # Enable TLS termination for the flower Ingress enabled: false # the name of a pre-created Secret containing a TLS private key and certificate secretName: "" extraEnv: | - name: AIRFLOW__CORE__LOAD_EXAMPLES value: 'True' - name: AIRFLOW__SCHEDULER__DAG_DIR_LIST_INTERVAL value: '30' webserverSecretKey: d9e40acbe90806dd6fc30d67edd3bdd0 volumes: - name: dags nfs: server: nfs.example.com path: /shared/gelcontent/airflow/dags - name: plugins nfs: server: nfs.example.com path: /shared/gelcontent/airflow/plugins - name: scripts nfs: server: nfs.example.com path: /shared/gelcontent/airflow/scripts volumeMounts: - mountPath: '/opt/airflow/dags' name: 'dags' - mountPath: '/opt/airflow/plugins' name: 'plugins' - mountPath: '/opt/airflow/scripts' name: 'scripts' logs: persistence: # Enable persistent volume for storing logs enabled: true   Now is time to deploy Airflow:   helm repo add apache-airflow https://airflow.apache.org helm upgrade --install airflow apache-airflow/airflow --namespace airflow --create-namespace -f ./values.yaml   Validate it is working by opening the URL which is the Ingress you specified earlier ("http://airflow.example.com/") and connecting using the default user admin/admin:     First thing to check is the presence of the SAS Airflow provider:       Demo   Now that we have Apache Airflow setup with the SAS Airflow provider and both Airflow and SAS Viya sharing the same DAGs directory, we can illustrate how SAS Viya and Airflow can work together. Let’s do this in video:     Some links of interest:   SAS Airflow Provider GitHub Repository: https://github.com/sassoftware/sas-airflow-provider     Find more articles from SAS Global Enablement and Learning here. ... View more

SAS Viya supports access to numerous databases. Each of them requires more or less effort regarding their configuration. Db2 has come up recently in several customer requests. Let’s walk through the steps of configuring SAS/ACCESS to Db2 in SAS Viya for both SAS Compute Server and CAS. While in this article I’ll give an overview of the procedure, I’ll also share detailed instructions in a separate document (see end of this article).     Native or JDBC/ODBC?   SAS/ACCESS to Db2 is not the easiest one I had to configure. Instead, I could have chosen JDBC to access Db2 because the configuration consists of copying a single file (the Db2 JDBC driver) into a specific directory and that would have been it. ODBC requires more steps.   But, by using alternative routes like ODBC or JDBC, you miss some important features that only the native access (SAS/ACCESS to Db2) can offer. And it’s all about performance: in-database SQL pushdown, data movement reduction, bulkloading and so on. That’s why it’s important to use the native way whenever you can.   Documentation   Before you start, checking the SAS documentation is always a good idea. You can check the requirements for Db2 here. Then, the configuration instructions are placed in a README file that you can access in 2 ways:   On https://my.sas.com, in your order’s “Downloads” section:   In the downloaded README, there’s a section named “Configuring SAS/ACCESS and Data Connectors for SAS Viya 4”. Or in your deployments assets once you have extracted them ($deploy/sas-bases/examples/data-access/README.md)   Download the Db2 client   It’s not always simple to find the right client to use for a database. Regarding Db2, you should be looking for something named “IBM Data Server Client” on the https://www.ibm.com/support/fixcentral/ portal. The following instructions give more information: https://www.ibm.com/docs/en/db2/11.5?topic=clients-installing-data-server-unix-linux.     Install the Db2 client   There are probably multiple options to get it done and no wrong approaches. But here is what I did to successfully install and configure the Db2 client in a Kubernetes context.   Create a dedicated user The Db2 client and configuration ultimately require to be owned by the sas user defined inside the SAS Viya deployment (through a NFS mount). This sas user has a UID and GID of 1001.   So, on the jump host or Kubernetes client that you are using to deploy SAS Viya, one approach is to create a new user account with a UID/GID of 1001 (or use the current 1001 UID user if one exists) and install the Db2 client with it.   Here is an example:     sudo groupadd -g 1001 sas sudo useradd -u 1001 -g 1001 sas -m sudo passwd sas # Choose a password for this account     NB: the user name sas is not essential, it could be another name.   Run the installer As the sas user you can then run the Db2 installer once you have extracted the Db2 client archive and install it in $HOME/sqllib:     ./db2_install -b $HOME/sqllib       Validate access to your Db2 database You can start defining your data sources on the client:     . $HOME/sqllib/db2profile db2cli writecfg add -database <db2_db> -host <db2_host> -port <db2_port> db2cli writecfg add -dsn <dsn_name> -database <db2_db> -host <db2_host> -port <db2_port>       And validate that the client is working fine with these definitions:     db2cli validate -dsn <dsn_name> -connect -user <db2-user> -passwd <db2-user-passwd>       Make the Db2 Client accessible from SAS Viya   The client is hopefully working fine on our jump host/client. We need to make it available to SAS Viya engines: SAS Compute and CAS.   Prepare the NFS structure To do that, we usually setup NFS mounts as described in the following README files (https://my.sas.com or in your deployment assets): $deploy/sas-bases/examples/sas-compute-server/configure/compute-server-add-nfs-mount.yaml $deploy/sas-bases/examples/cas/configure/cas-add-nfs-mount.yaml   To make things easy, mount this NFS directory on your machine (jump host/deployment machine) exactly as it is mounted in SAS Viya (same path). Then prepare 2 directories for Db2: /nfs-path-to-sas-access-clients/db2client /nfs-path-to-sas-access-clients/db2   Make them owned by the sas user (1001 UID).   Copy Db2 client files to the NFS shared location As sas, copy the Db2 client from the local installation to the NFS directory (-L to materialize all links as real files and directories):     cp -R -L $HOME/sqllib /nfs-path-to-sas-access-clients/db2client/sqllib       Copy client configuration files Adjust 2 variables (DB2DIR and INSTHOME) in the db2profile file on NFS:     export DB2_NET_CLIENT_PATH=/nfs-path-to-sas-access-clients/db2client/sqllib sed -i 's|^DB2DIR=.*|DB2DIR='$DB2_NET_CLIENT_PATH'|g' $DB2_NET_CLIENT_PATH/db2profile sed -i 's|^INSTHOME=.*|INSTHOME=/nfs-path-to-sas-access-clients/db2|g' $DB2_NET_CLIENT_PATH/db2profile       Run the copy client profile command:     source $DB2_NET_CLIENT_PATH/db2profile export DB2_APPL_DATA_PATH=/nfs-path-to-sas-access-clients/db2 export DB2_APPL_CFG_PATH=/nfs-path-to-sas-access-clients/db2 $DB2_NET_CLIENT_PATH/bin/db2ccprf -f -t /nfs-path-to-sas-access-clients/db2       This utility copies configuration files in the /nfs-path-to-sas-access-clients/db2 directory.   At this stage, you should have your 2 Db2 directories (db2client and db2) populated, owned by sas, and accessible from SAS Viya.     Update SAS Viya   The last step is to update SAS Viya with some required Db2 environment variables so that SAS Compute Server and CAS can find the Db2 client.   Follow the steps mentioned in the section named “Configuring SAS/ACCESS and Data Connectors for SAS Viya 4 => Installation => Set Environment Variables” of the README file.   Basically, you have to update the sample sas-access.properties with variables needed by Db2:     ########################## # SAS/ACCESS to DB2 ########################## CUR_INSTHOME= CUR_INSTNAME= DASWORKDIR= DB2DIR=${DB2CLIENTPATH} DB2INSTANCE=sas DB2LIB=${DB2CLIENTPATH} DB2_HOME=${DB2CLIENTPATH} DB2_NET_CLIENT_PATH=${DB2CLIENTPATH} IBM_DB_DIR=${DB2CLIENTPATH} IBM_DB_HOME=${DB2CLIENTPATH} IBM_DB_INCLUDE=${DB2CLIENTPATH} IBM_DB_LIB=${DB2CLIENTPATH} INSTHOME=${DB2PATH} INST_DIR=${DB2CLIENTPATH} PREV_DB2_PATH= DB2=${DB2CLIENTPATH}/lib64:${DB2CLIENTPATH}/lib64/gskit:${DB2CLIENTPATH}/lib32 DB2_BIN=${DB2CLIENTPATH}/bin:${DB2CLIENTPATH}/adm:${DB2CLIENTPATH}/misc     Where DB2CLIENTPATH=/nfs-path-to-sas-access-clients/db2client/sqllib and DB2PATH=/nfs-path-to-sas-access-clients/db2   NB: the DB2INSTANCE variable is purposely set to sas which is the predefined user that SAS Viya uses behind the scenes. It is mapped automatically to the uid=1001, regardless of the username chosen earlier.   Then you need to apply this modification to your SAS Viya deployment according to your deployment method (see https://communities.sas.com/t5/SAS-Communities-Library/New-SAS-Viya-Deployment-Methods/ta-p/856206).     Test SAS/ACCESS to Db2   After having restarted CAS and started a new SAS Compute session, you are ready to test. You can adapt the following SAS code to start your validation: /* Check some environment variables */ %put %sysget(DB2) ; %put %sysget(INSTHOME) ; %put %sysget(INST_DIR) ; /*** SAS Compute Server ***/ /* Native Db2 */ /* sampledsn is a dsn example defined earlier using db2cli writecfg */ libname ndb2 db2 database="sampledsn" user="myuser" password="mypw" ; /* Other syntaxes */ /* libname ndb2 db2 noprompt="DSN=sampledsn;UID=myuser;PWD=mypw;" ; libname ndb2 db2 noprompt="Hostname=mydb2server;Database=sample;ServiceName=50000;UID=myuser;PWD=mypw;" ; */ /* List Db2 tables */ proc datasets lib=ndb2 ; quit ; /*** CAS ***/ cas mysession ; /* Drop caslib if exists */ proc cas ; dropcaslib / caslib="casndb2" quiet=true ; quit ; /* Native Db2 */ /* sampledsn is a dsn example defined earlier using db2cli writecfg */ caslib casndb2 datasource=(srctype="db2" database="sampledsn" username="myuser" password="mypw" schema="MYUSER") libref=casndb2 ; /* Other syntaxes */ /* caslib casndb2 datasource=(srctype="db2" conOpts="DSN=sampledsn;UID=myuser;PWD=mypw;" schema="MYUSER") libref=casndb2 ; caslib casndb2 datasource=(srctype="db2" conOpts="Hostname=mydb2server;Database=sample;ServiceName=50000;UID=myuser;PWD=mypw;" schema="MYUSER") libref=casndb2 ; */ /* List Db2 tables */ proc casutil incaslib="casndb2" outcaslib="casndb2" ; list files ; quit ; cas mysession terminate ;   If you are looking for a more detailed procedure and experiment it, feel free to check the attached document out.   Thanks for reading. ... View more

It has already been almost 8 years since Hadoop reached its peak in terms of interest in web searches. Spark then took over and Hadoop/Spark services are still widely used at numerous companies.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   SAS always tries to keep providing support for a countless number of data providers. And recently, Google Dataproc has been added to the data sources SAS Viya can work with.   What is Google Dataproc?   “Google Dataproc is a fully managed and highly scalable service for running Apache Hadoop, Apache Spark, and 30+ open source tools and frameworks.”   Dataproc is very accessible and you can provision a ready-to-use cluster in 90 seconds.     When did SAS Viya start supporting Google Dataproc?   Hadoop (Hive) support for Google Dataproc has been introduced in SAS Viya Stable Release 2022.11 through the use of the included CData JDBC driver for Apache Hive.     What are the configurations steps?   The following steps have been tested with a Google Dataproc cluster setup on Compute Engines. Not discussed here are the required steps to allow network communication between SAS Viya and Google Dataproc (it highly depends on your setup).     Collect the configuration and JAR files This step is recommended if you want to access HDFS under the covers, but not mandatory. Indeed, bulkload and bulkunload capabilities will be unlocked if HDFS can be used by SAS for staging data during read and write operations. HDFS access requires SAS to have access to certain configuration and JAR files collected beforehand.   To do this task, we will need to run the Hadoop Tracer Script on the Google Dataproc cluster’s name node.   The Hadoop Tracer Script can be downloaded from http://ftp.sas.com/techsup/download/blind/access/hadooptracer.zip. It has been recently updated to work with Google Dataproc.   We need to copy this zip file to the Dataproc’s name node and unzip it. The archive contains a Python script and a required json file.   The Hadoop Tracer Script makes use of the strace tool which needs to be install prior to running the script (" sudo apt-get install strace -y " for example on the Debian image used by Google Dataproc).   We are ready to run the script using the following command:   python ./hadooptracer_py --filterby=latest --postprocess --jsonfile ./driver.json --jars ./jars --conf ./conf --logfile ./tracer.log   This script can take 15 minutes to run. At the end, you should have a collection of configuration and JAR files in the ./jars and ./conf relative sub-folders on Dataproc’s name node.     Make the files available to SAS Compute Server and CAS   The collected files need to be available to Compute and CAS. Therefore, you need to copy them from the Dataproc name node to a location that will be available to both through a K8s Persistent Volume or a NFS mount.   Once done, you are ready to use Google Dataproc from SAS Viya.     How to connect to Google Dataproc from SAS Viya?   To access Google Dataproc from SAS Compute, you first need to assign two environment variables to point to the Hadoop configuration and Jar files collected earlier (again recommended, not mandatory). You also need the IP address of the Dataproc cluster’s name node. Then you can use the Hadoop libname engine along with the CData Apache Hive JDBC driver included in SAS Viya:   option set=SAS_HADOOP_JAR_PATH="/dataproc/jars" ; option set=SAS_HADOOP_CONFIG_PATH="/dataproc/conf" ; libname dataproc hadoop user="hdfs" password="pw" read_method=hdfs driverclass="cdata.jdbc.apachehive.ApacheHiveDriver" uri="jdbc:apachehive:Server=<ip-address>;QueryPassthrough=True;Database=default;DefaultColumnSize=1024" ;   From CAS, the CASLIB statement will look similar to the Hadoop libname:   caslib casdp datasource=(srctype="hadoop" username="hdfs" password="pw" uri="jdbc:apachehive:Server=<ip-address>;QueryPassthrough=True;Database=default;DefaultColumnSize=1024" hadoopJarPath="/dataproc/jars" hadoopConfigDir="/dataproc/conf" ) libref=casdp ;   The hadoopJarPath and hadoopConfigDir replace the two environment variables used in SAS Compute.     Now you are ready to read/process/write Google Dataproc data from SAS Viya.   Special thanks to my colleague Bill Oliver for his help on this topic.     Thanks for reading.   Find more articles from SAS Global Enablement and Learning here. ... View more