In Part 1, we introduced the concept of elasticity as it applies to CAS. Now, let's explore the setup and demonstrate how table rebalancing operates.     Setup for CAS Table Rebalancing   Enabling table rebalancing is only necessary when adding more workers or when a failed worker node is restarted.   For a detailed setup guide, Gilles provides step-by-step instructions in his post.   To activate table rebalancing, set the following environment variables to "true" in CAS:   CAS_GLOBAL_TABLE_AUTO_BALANCE for permanent CAS tables SESSION_TABLE_AUTO_BALANCE for session CAS tables   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Indeed, you may choose to apply rebalancing only to global tables, as session tables may not always need it.   These variables unlock the rebalancing feature, but additional configuration is required to specify which tables should be rebalanced.   Now, SAS Administrators or Programmers must designate which tables are eligible for rebalancing when more workers are added. This is controlled by the tableRedistUpPolicy option, which has two settings:   NOREDIST: Prevents redistribution of table data when the number of worker pods increases. REBALANCE: Enables rebalancing of table data when the number of worker pods increases.   You can set tableRedistUpPolicy at various levels, depending on whether you need a global or table-specific configuration. Here’s the order of precedence:   Order of precedence Option Scope Example 1 tableRedistUpPolicy CAS action casout option Table proc cas ;    simple.freq /       table={caslib="DM", name="prdsale"},       inputs={"country"},       casOut={caslib="DM", name="country_freq", tableRedistUpPolicy="REBALANCE"} ; quit ; 2 tableRedistUpPolicy CASLIB option CASLIB caslib DM datasource=(srctype="path") path="/gelcontent/" tableRedistUpPolicy=REBALANCE ; 3 sessionTableRedistUpPolicy CAS session option Session table in a CAS session cas mysession sessopts=(sessionTableRedistUpPolicy=rebalance) ; 4 cas.TABLEREDISTUPPOLICY CAS option CAS server cas.TABLEREDISTUPPOLICY = ‘REBALANCE’     How Does CAS Table Rebalancing Work?   Let’s explore how CAS table rebalancing affects each of our four scenarios. Watch the videos below to see rebalancing in action.   Adding Workers for Peak Activity   When a SAS Administrator adds one or more new CAS workers to handle a surge in activity.       Removing Workers to Return to Normal   When a SAS Administrator removes one or more CAS workers to scale back to normal resource levels.       Handling Node Failure with Automatic Restart   When a CAS worker fails (node failure), and Kubernetes automatically restarts a new one to maintain the right number of workers.       Handling Node Failure Without Restart   When a CAS worker fails, but Kubernetes is unable to restart a replacement within 60 seconds, triggering rebalancing on the remaining nodes.       That's it for this one. In the final part, we will cover additional considerations regarding CAS table rebalancing.   Thanks for reading!     Find more articles from SAS Global Enablement and Learning here. ... View more

Yes, it is, and that's a big deal! Elasticity is crucial for any cloud-deployed software, as it enables resources to scale up during peak periods, allowing more users to access the system, handling larger jobs, or processing more simultaneous tasks. When demand drops, resources scale down, saving costs and optimizing performance.   Since the release of CAS's initial capabilities regarding that matter, Gilles' insightful post, CAS Server Topology Changes and CAS Table Balancing, has been essential reading. Now, let’s dive deeper into what makes CAS elastic, explore real-world use cases, and provide a guide to setting up table rebalancing and what it means for users.     Understanding Elasticity in Computing   As Google Chrome’s AI Overview puts it, "Elastic computing is a system's ability to adjust its resources to match the current demand for processing, memory, and storage."   While this definition implies automated resource adjustments, CAS handles elasticity a bit differently. CAS doesn’t automatically reconfigure resources out of the box. However, with some clever use of SAS Viya monitoring tools and a bit of customization, automation could be within reach. But that’s a story for another day!   So, what’s a good definition of CAS elasticity? Here’s mine: "CAS’s ability to seamlessly accommodate the addition or removal of worker nodes without disrupting user activities, while efficiently utilizing all nodes in the cluster for balanced data distribution and parallel processing."   Let’s put this definition to the test.   What Makes CAS Elastic?   Before diving into specifics, let’s understand the CAS capability that enables us to say it’s elastic.   In a Massively Parallel Processing (MPP) CAS cluster, data is distributed across multiple workers—spread evenly and randomly by default—allowing for maximum parallelization of workloads, with each worker handling an equal share of data.   One of CAS’s powerful features is the ability to add more workers to an existing cluster without stopping the system. This is great. However, if you expand a 3-worker cluster to a 6-worker one, by default, your data remains on the original 3 workers. As a result, only those 3 workers are utilized for data processing, leaving the new 3 workers underused. This means you’re not fully optimizing the expanded CAS environment.   Conversely, if you scale down (say from a 6-worker cluster to a 4-worker cluster—not possible without stopping CAS until recently), any CAS tables without sufficient copies would become unusable unless a crucial operation takes place behind the scenes.   This essential operation is called automatic data redistribution, or table rebalancing.   As the name suggests, this feature automatically redistributes or reshuffles data blocks across the new set of CAS workers whenever an administrator adds or removes workers. This ensures all available workers are engaged, maximizing the performance and efficiency of the CAS environment.     Is Changing the Number of CAS Workers Easy?   You might be wondering, "This all sounds interesting, but how does my SAS admin actually change the number of CAS workers? If it’s as complicated as I’ve heard, it may not be worth it." The good news? It’s simpler than you might think. Changing the number of CAS worker nodes only requires a single kubectl command, as shown below (documented here)   kubectl -n name-of-namespace patch casdeployment name-of-casdeployment --type=json -p='[{"op": "add", "path": "/spec/workers", "value": number-of-worker-nodes}]'   For instance, to set the number of workers to 5, an admin would run:   kubectl -n viya patch casdeployment default --type=json -p='[{"op": "add", "path": "/spec/workers", "value": 5}]'   That’s it! Once this command is executed, CAS will add or remove workers to match the specified number of workers, making the process quick and efficient.     Use Cases for CAS Elasticity   Before we dive into enabling this capability, let’s look at scenarios where CAS elasticity could come into play. We’ve identified four key situations:   A SAS Administrator adds one or more CAS workers to handle a peak in activity. A SAS Administrator removes one or more CAS workers to return to normal resource levels. A CAS worker fails (node failure), and Kubernetes automatically restarts a new worker. A CAS worker fails (node failure), but Kubernetes is unable to restart a replacement.   For scenarios 2 and 4 (reducing the number of workers), no extra configuration is required. As of version LTS 2023.10, CAS automatically rebalances tables on fewer workers out of the box.   In scenario 4, however, ensure you have the COPIES setting (with COPIES ≥ 1) enabled to maintain table availability during node failure, allowing tables to rebalance across the remaining workers.   For scenarios 1 and 3 (increasing the number of workers, although in scenario 3, a worker is lost before being replaced), automatic rebalancing requires additional configuration.   It’s also worth noting that CAS no longer needs to be stopped when adding or removing workers. In short, your SAS Viya platform remains operational by default:   Built-in CAS table rebalancing when workers are lost. Optional CAS table rebalancing when adding workers (even without rebalancing, tables remain accessible and functional as more workers are added).   That's it for today. In the next parts, we will cover the setup of table rebalancing, illustrate how it works and give some additional considerations.   Thanks for reading! ... View more

In the past, I shared insights on how to publish and execute SAS scoring models in both Databricks and Azure Synapse Analytics:   Publish and Run a SAS Scoring Model In Azure Databricks   Publish and Run a SAS Scoring Model In Azure Synapse Analytics   Back then, deploying these models required using a cloud object storage location. The models would be accessed by the target platforms through a proprietary mechanism, such as a mount or link.   Previous Databricks (on Azure) Scoring Workflow   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   While this approach worked, it involved multiple connectivity points and mechanisms that might no longer be recommended by the vendor (for instance, Databricks has phased out the use of DBFS mounts). This added unnecessary complexity and occasionally led to errors.   The New and Improved Process   Starting in LTS 2024.03, publishing SAS scoring models has become much simpler for Databricks (both Azure and AWS), Azure Synapse Analytics, and Azure HDInsight. You can now publish models directly to a table within the target platform:   Databricks: Publish to a Spark table Azure Synapse Analytics: Publish to a SQL Server table Azure HDInsight: Publish to a Hive table   Let's revisit the Databricks example to see how the scoring process looks now:     Now, a single CASLIB manages both the publishing and execution of the scoring model, significantly reducing complexity.   Code Example: Publishing and Running a Model in Databricks   Here’s how you can publish a model to a Spark table and execute it in Databricks:   /* Create a Spark caslib */ caslib spark datasource= ( srctype="spark", platform=databricks, bulkload=no, server="&SERVER", clusterid="&CLUSTERID", username="&USERNAME", password="&AUTHTOKEN", jobManagementURL="&JOBMANAGEMENTURL", httpPath="&HTTPPATH", properties="Catalog='&DB_CATALOG';UseLegacyDataModel=true;Other=ConnectRetryWaitTime=20;DefaultColumnSize=1024", schema="&DB_SCHEMA" ) libref=spark ; /* Publish a model in a Spark table */ proc scoreaccel sessref=mysession ; publishmodel exttype=databricks caslib="spark" modelname="gradboost_astore" storetables="spark.gradboost_store" replacemodel=yes ; quit ; /* Start the SAS Embedded Process */ proc cas ; sparkEmbeddedProcess.startSparkEP caslib="spark" ; quit ; /* Run the model stored in a Spark table */ proc scoreaccel sessref=mysession ; runmodel exttype=spark caslib="spark" modelname="gradboost_astore" modeldatabase="&DB_SCHEMA" intable="hmeq_prod" schema="&DB_SCHEMA" outtable="hmeq_prod_out_astore" outschema="&DB_SCHEMA" forceoverwrite=true ; quit ;   Naming Convention Update   When publishing a model to a Spark table, the table will follow a consistent naming convention by prefixing the model's name with "sasmodel_.":     Conclusion   This update simplifies the SAS scoring model deployment process by consolidating the steps and reducing connectivity issues, making it easier to integrate SAS predictive analytics into your Databricks, Azure Synapse, or Azure HDInsight environment.   Thanks for reading!     Find more articles from SAS Global Enablement and Learning here. ... View more

After first part, let’s continue our journey on identifying where to define SAS libraries.   So far, we have discussed:   The global way The contextual way The personal way   The UI way   Libraries can also be defined using a dedicated User Interface in SAS Studio. This is what Gerry’s blog mentioned in the first part is about.     Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Multiple cases can then happen:   A user with no administrative privilege creates a library and doesn’t check “Assign and connect to data sources at startup”     This library is created in the Data Sources microservice. Thus, it is available to a SAS Administrator in SAS Environment Manager as a resource that could be attached to a context (see later).   The library will be available as a "Disconnected Library" at next user’s session, regardless of the context. It is tied to a user.   It is not added to the user’s SAS Studio autoexec.   A user with no administrative privilege creates a library and does check “Assign and connect to data sources at startup”     This library is created in the Data Sources microservice. Thus, it is available to a SAS Administrator in SAS Environment Manager as a resource that could be attached to any context (see later).   The library will be available as a “Connected Library” at next session for this user, regardless of the context, because it has been added to the user’s SAS Studio autoexec, through the LIBDEF= LIBNAME option.     A user with administrative privilege creates a library, checks “Allow all users to view the library connection” but doesn’t check “Assign and connect to data sources at startup”     This library is created in the Data Sources microservice. Thus, it is available to a SAS Administrator in SAS Environment Manager as a resource that could be attached to any context (see later).   This library is added to the current context (the one that the user is currently connected to) as a resource. So, it will be available to any user starting a session with this context, as disconnected or as connected (depending on the choice made in the radio box).   It is not added to the user’s SAS Studio autoexec, so it will not be available if the user switches to another context.   A user with administrative privilege creates a library, checks both “Assign and connect to data sources at startup” and “Allow all users to view the library connection”     This library is created in the Data Sources microservice. Thus, it is available to a SAS Administrator in SAS Environment Manager as a resource that could be attached to any context (see later).   This library is added to the current context (the one that the user is currently connected to) as a resource. So, it will be available to any user starting a session with this context, as disconnected or as connected (depending on the choice made in the radio box).   The library will be available as a “Connected Library” at next session for this user, regardless of the context, because it has been added to the user’s SAS Studio autoexec, through the LIBDEF= LIBNAME option.     This is where some confusion can occur because the library is attached to a context and is also defined in the user’s autoexec. So, if the user starts a session using the original context (the one that was used to define the library), the library is assigned twice (the first time from the context, the second time from the autoexec). In reality, it doesn’t really matter. The user sees only one library. But this helps understand how libraries are assigned.   Impacts of the UI way   Now that we know how to use the UI to define libraries, additional questions arise.     Where do I see that a library defined in the UI has been attached to a context?   Indeed, by checking “Allow all users to view the library connection”, an admin adds a library to the current context. But how does this materialize?   In SAS Environment Manager, you can observe the results of that action in the context:     The libraries associated with a context show up under Resources. The Assign check box depends on how the library has been set up (Add as disconnected / Add as connected). Thus, you can have libraries defined under Resources and libraries defined under Advanced in the autoexec.     How can I add data source definitions to other contexts?   Since data source definitions are available globally to a SAS admin, he can decide to attach them to any context. For example, if he wants to add the ADCONT1 and ADCONT2 libraries to another context (for example the SAS Job Execution compute context), he will edit the compute context, go into Resources, then click on the + sign:     Then, the SAS admin will select the definitions:     And customize them if needed (assign them by default, rename them):       Instead of providing all the connection details in a SAS code autoexec, can I use the library definitions from the UI (using the LIBDEF= LIBNAME option) at a global level (compute for example)?   Yes, I can. I just need to find the right data source definition URI. The quicker way is to check “Assign and connect to data sources at startup” when defining a data source in the UI and check the SAS Studio autoexec right after. You should see the exact syntax of what you would use:     Then you can edit the compute server (like shown in “the global way”, previous blog) and add the library using the LIBDEF= syntax:       Where can I find all data source libraries defined in the UI by SAS users and how can I obtain their URI?   An admin can list them when trying to add a library to a context (Select a resource dialog show above). But this won’t give the URI needed by the LIBDEF= LIBNAME option.   The other option is to use the SAS Viya REST API to list all data source definitions and eventually obtain their URI.   Here are some examples:   # Using curl # List all curl -k "https://${SAS_VIYA_END_POINT}/dataSources/providers/Compute/sourceDefinitions" \ -H "Authorization: Bearer $VIYA_ACCESS_TOKEN" # Query one curl -k "https://${SAS_VIYA_END_POINT}/dataSources/providers/Compute/sourceDefinitions?filter=eq(name,'ADMIN_CONTEXT2')" \ -H "Authorization: Bearer $VIYA_ACCESS_TOKEN" # Using pyviyatools https://github.com/sassoftware/pyviyatools callrestapi.py -m get -e "/dataSources/providers/Compute/sourceDefinitions?filter=eq(name,'ADMIN_CONTEXT2')"     Here is a sample output:       Recap   We have been able to see that we can define libraries at different levels and scopes:   At the server level (compute, batch, connect, etc.) At the context level (for compute servers) At the user level for SAS Studio (which works across contexts)     We have also observed that libraries defined using the “New Library Connection” wizard in SAS Studio can be managed, attached to compute contexts as Resources and used in library assignment with the LIBDEF= LIBNAME option.     What’s next?   In the future, a central and universal approach will be used to create and manage data source connections for both SAS Cloud Analytic Services (CAS) and SAS servers (compute, batch, etc.). One unique connection will be used to define both a CASLIB (for CAS) and a library (for SAS servers). This will improve the user experience and greatly reduce the maintenance of data source definitions in SAS Viya.   This new “Connections” component already exists for CAS connections only (it is NOT yet available for SAS libraries in SAS Studio) and is available in the “Manage Data” menu (SAS Data Explorer):     Defining and maintaining a data source connection becomes easier, fully guided, with a quick access to a lot of options, quick access to the documentation and requirements directly from the connection dialog, ability to search for options and to see what changes have been made.       Thanks for reading.     Find more articles from SAS Global Enablement and Learning here. ... View more

In SAS Viya, you have multiple ways to define your SAS libraries. In this post, we will walk through the different ways of creating SAS libraries in SAS Viya and describe what their benefits are.   @GerryNelson wrote a nice post some time ago about managing SAS libraries using the “New Library Connection” dialog in SAS Studio. Please check it out to get more details about some approaches explained later, especially in part 2.   @DavidStern wrote a very useful post too that will help understand the scope of the various ways to define libraries.   Finding a way to set up a library is still highly correlated to finding where to set up an autoexec in SAS Viya. We’ll see that this is slowly changing as new approaches become available.   The global way   In SAS Viya, we have multiple ways to start a SAS session depending on how we instantiate it. SAS Studio users will start a compute server. Automated tasks, run through the SAS Viya CLI for example, will probably start a batch server. SAS 9 users might start a connect server in SAS Viya.   So, a server is the highest level for defining an autoexec and thus SAS libraries.   You can specify an autoexec and SAS libraries in SAS Environment Manager for the specific server. Below is an example of an autoexec set up for the compute server:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.     Defining a library at this level means it will be available globally to all users starting a compute server. So, it is a good way to make your shared libraries available to all SAS users, regardless of the context they will be using.   The same configuration exists for batch and connect servers. If you want this library to be available everywhere, then you would also have to add it to the batch and connect autoexec. The configuration steps are similar.   The contextual way   Under the server, there is this concept of context. A compute server is run under a compute context. A batch server is run under a batch context. Etc. A context provides additional specific configuration to run a particular server. By definition, “a compute context is a specification that contains the information that is needed to run a compute server”.   Contexts exist for compute, batch and connect servers. However, only compute contexts allow an admin to specify an autoexec.   There are some pre-existing contexts out-of-the-box:     SAS Studio compute context is the default context used when you open SAS Studio.   You can create your own contexts (group-based, project-based), for example to provide dedicated resources to groups of users, or to a specific project. And then provide them with different libraries, or same library names pointing to different locations.   You still use SAS Environment Manager to define an autoexec on a context like in this example below:     The personal way   In addition, a SAS Studio user can add his own library definitions in his own private autoexec. This autoexec is not tied to a computing environment but rather to a user. This means that, regardless of the context the user chooses, the libraries defined in his autoexec will always be available to him (in SAS Studio). We’ll see in the recap, part 2, how the different scopes work together.   Here is how to add libraries to one’s SAS Studio autoexec:     At this point, in SAS Studio, I have 3 different libraries coming from 3 different levels of configuration:     Stay tuned for the second part of this article where we will explore additional ways of creating libraries and how they impact what we have seen so far.   Thanks for reading. ... View more

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