Navigating through a sea of data assets can be a daunting task. SAS Information Catalog is your navigator in this journey, allowing you to discover, search, and manage your SAS Viya assets efficiently. When a data asset is discovered, hundreds of metrics are calculated. Imagine having the ability to access these metrics through a simple command, downloading them using a Catalog API. This opens up the possibility of using these rich metrics in custom reports or flows to answer pressing questions.   Metadata Levels   When utilizing the SAS Information Catalog REST API to download metadata for data assets, tables, or files, you have several options to choose from. The metadata can be of various types, such as:   dataDictionary: provides basic column-level metadata, such as name and type. dataDictionaryAndProfile: offers extensive metadata that combines both data dictionary and profile metrics. detailedMetrics: gives comprehensive metadata at a column level, including patterns, frequency distribution values.   The middle level, dataDictionaryAndProfile, strikes an excellent balance between richness and complexity, making it the ideal choice if you need to identify private data, semantic type or classification at a column level.   Download Metadata   Recorded Demonstration   For a more visual understanding of the process, you can watch the following recorded demonstration:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Get a SAS Viya Access Token   For information on obtaining a SAS Viya access token, refer to the previous post Discover Your Data with SAS Information Catalog APIs from Python – Access.   Python Program   The Python program download_metadata.py retrieves and processes metadata from a SAS Viya Information Catalog, using REST API:   Imports necessary packages. Constructs Catalog API URL for the request. Retrieves a saved access token. Sends the Catalog API request. Saves the response to a CSV file.   Here’s the complete code:   # 1 Packages import sys import requests import json import os # 2. Arguments print ("Number of arguments:", len(sys.argv), "arguments") print ("Argument List:", str(sys.argv) + '\n') baseURL=str(sys.argv[1]) search_query=str(sys.argv[2]) pem_path=str(sys.argv[3]) # 3. Construct Variables print ("REST API Inputs:\n") print('\nYour SAS VIYA host is ', baseURL) print('\nCatalog API URL: ') print(f'{baseURL}/catalog/instances' + search_query) url = f'{baseURL}/catalog/instances' + search_query print('\nDownload metadata specified in the URL: ' + url + '\n') """ # URL Examples: Select one of the following - passed as a parameter # Download profile Metrics filtered by name with prefix # url = f"{baseURL}/catalog/instances/?filter=startsWith(name,'WATER')&?filter=contains(type,cas)&level=dataDictionaryAndProfile&limit=10" # Download detailedMetrics filtered by name with prefix # url = f"{baseURL}/catalog/instances/?filter=contains(type,cas)&level=detailedMetrics&limit=10" # Download profile Metrics filtered by name with prefix # url = f"{baseURL}/catalog/instances/?filter=contains(type,cas)&level=dataDictionary&prefix=simpledownload&limit=100" """ # 4. Get the Saved Access Token print('\nRetrieving the saved token from api/access_token.txt\n') with open("api/access_token.txt", "r", encoding="UTF-8") as f: token = f.read() #print(token) headers = {'Accept': 'text/csv','Authorization': 'Bearer ' + token} # Create folder directory = 'api' if not os.path.exists("api/"): os.mkdir("api/") # Save in catalog_download.csv file utf-8 ## stream the API requests with open(directory + '/catalog_download.csv', 'w', encoding="utf-8") as f: with requests.get(url, headers=headers, stream=True, verify=pem_path) as response: f.write(response.text) f.close() print('\nThe Catalog Downloaded Metadata was stored for you as ' + directory + '/catalog_download.csv\n')   This program demonstrates how to use the SAS Viya REST API to perform a metadata download. It provides a basic example and can be used as a starting point for building more advanced functionality.   Run the Program   The program expects the following command-line arguments:   The hostname of the SAS Viya server. The metadata level and additional filters. The path to the PEM file for TLS certificate verification.   Metadata Level dataDictionaryAndProfile   In a Bash terminal on a Windows machine, you can run the program with command-line arguments:   # Certificate on a Windows machine and executable is python python download_metadata.py https://sas_viya_url "?filter=startsWith(name,'detailed')&?filter=contains(type,cas)&level=dataDictionaryAndProfile&limit=10" "C:\\Users\\myuser\Downloads\\gelenv_trustedcerts.pem"   Running the program with the provided parameters will download the metadata and save it in a CSV file.   The filter downloads only metadata for data assets:   Where name starts with ‘detailed’ Their type = ‘cas’. This is broader than just a CAS table, it implies a data asset, a table or a file. Limit to 10 (data assets) matching the criteria. With the metadata level dataDictionaryAndProfile.   A SAS Viya certificate is used here, in the form of a PEM file. The PEM file was copied in the 'C:\Users\...\' folder.   In a Bash terminal on a Linux machine, the above statement would be:   # Certificate on a Linux machine and executable is python3 python3 download_metadata.py https://sas_viya_url "?filter=startsWith(name,'detailed')&?filter=contains(type,cas)&level=dataDictionaryAndProfile&limit=10" /home/cloud-user/.certs/gelenv_trustedcerts.pem   A SAS Viya certificate is used here, in the form of a PEM file. The PEM file is assumed to be present in the '/home/myuser/' folder.   Output You will see many valuable characteristics at a column level, such as:   The calculated classification. If private data was detected in the column. Languages detected. Sentiment detected. Keywords extracted. Classic profiling measures, etc.     The table metadata, such as table keywords, tags, most important columns, privacy and so on, is repeated for each column.   Metadata Level dataDictionary   In a Bash terminal on a Windows machine, you can run the program with command-line arguments.   python download_metadata.py https://sas_viya_url "?filter=startsWith(name,'detailed')&?filter=contains(type,cas)&level=dataDictionary&limit=10" "C:\\Users\\myuser\Downloads\\gelenv_trustedcerts.pem"   Output You shall see simple column metadata. The table metadata is repeated for each column.     Metadata Level - detailedMetrics   In a Bash terminal on a Windows machine, you can run the program with command-line arguments.   python download_metadata.py https://sas_viya_url "?filter=startsWith(name,'detailed')&?filter=contains(type,cas)&level=detailedMetrics&limit=10" "C:\\Users\\myuser\Downloads\\gelenv_trustedcerts.pem"   Output You will see many values and detailed characteristics at a column - metric - value level.     Conclusion   This program demonstrated how to use the SAS Viya REST API to download the metadata at column level. Stay tuned for more in-depth discussions and tutorials in this series.   Acknowledgements   Nancy Rausch, R&D.   Lavanya Ganesh, R&D.   @AchalPatel, early pioneer of the Download Catalog API.    Additional Resources   You might find the following resources helpful. Read:   SAS Information Catalog: Administrator’s Guide. Discover Your Data with SAS Information Catalog APIs from Python – Access. Leveraging SAS® Information Catalog REST APIs: Programmatically Discovering Data.   Watch:   SAS Demo | Top Data Questions You Can Answer with SAS Information Catalog. Discover Information Assets with SAS Information Catalog | SAS Viya Quick Start Tutorial. How to Use SAS Information Catalog.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email.     Find more articles from SAS Global Enablement and Learning here. ... View more

Navigating through a sea of data assets can be a daunting task. SAS Information Catalog is your navigator in this journey, allowing you to discover, search, and manage your SAS Viya assets efficiently. When a data asset is discovered, hundreds of metrics are calculated. Imagine accessing these metrics with a simple command, downloading them, or even using them in a report or a flow. This blog series will guide you through the process of leveraging the SAS Information Catalog REST APIs from Python to learn more about your SAS Viya data assets. This post, in particular, will focus on acquiring an access token using a method revised in 2023.     SAS Information Catalog in Two Minutes   If you're new to SAS Information Catalog, here's a quick rundown. If you prefer to watch a video, check out SAS Demo | Top Data Questions You Can Answer with SAS Information Catalog or How to Use SAS Information Catalog. If you prefer to read, the catalog: Allows you to discover, search, and manage your SAS Viya assets, such as data, tables, files, SAS Visual Analytics reports, SAS models, SAS decisions, SAS Studio flows, or code files. You can search the catalog for your assets using keywords (free text search) or calculated metrics and their values (faceted search). You can also view the information privacy status of your assets, which is calculated by the catalog. The catalog produces numerous metrics about your data assets at both the table and column levels. Agents, a set of jobs, drive the discovery process. They list the contents of a library, apply any filters, and then start calculating the metrics. These metrics are then sent to an index that you can use for searching.   Why Use Catalog APIs?   Despite having comprehensive interfaces in SAS Information Catalog, there might be a need for custom reporting or workflows based on specific asset characteristics. SAS Information Catalog APIs, now made public, are the solution to this challenge.   Get an Access Token   Despite the availability of resources, getting a SAS Viya access token can sometimes be confusing and many users get stuck at this stage. The SAS Software GitHub page explains a simple method to get an access token to work with SAS Viya REST APIs.   However, there's a more secure and efficient method, described by Joe Furbee in Register Client and Create Access Token. I prefer this method as it excludes the password from the Python program, making the program more "Git-ready". The method also registers a client, uses an authorization code, and a TLS certificate to secure data in transit.   Recorded Demonstration   For a more visual understanding of the process, you can watch the following recorded demonstration:     Python Program Here’s the complete code to get an access token and a refresh token. # Run instructions """ Using Joe Furbee's approach: https://github.com/sassoftware/rest-api-use-cases/blob/main/python/authentication/registerClient_generateToken_Python_2023.ipynb https://blogs.sas.com/content/sgf/2023/02/07/authentication-to-sas-viya/ """ print('\nGetting parameters from the command line\n') # 1 Packages import requests, json, os, base64, sys # 2. Arguments print ("Number of arguments:", len(sys.argv), "arguments\n") #print ("Argument List:", str(sys.argv)) baseURL=str(sys.argv[1]) client_id=str(sys.argv[2]) client_secret=str(sys.argv[3]) admin_user=str(sys.argv[4]) admin_password=str(sys.argv[5]) pem_path=str(sys.argv[6]) print() print ("\nWe are going to use a certificate: ", pem_path) # 3. Construct Variables authUri="/SASLogon/oauth/token" print('\nYour SAS VIYA host is ', baseURL) print ('\nAuth URL', authUri) # Get a BEARER_TOKEN to delete and register a client print('\nGet a BEARER_TOKEN to register a client\n') # generate API call for register access token url = f"{baseURL}/SASLogon/oauth/token" payload = f"grant_type=password&username={admin_user}&password={admin_password}" headers = { 'Content-Type': 'application/x-www-form-urlencoded', 'Authorization': 'Basic c2FzLmNsaTo=' } # process the results response = requests.request("POST", url, headers=headers, data=payload, verify=pem_path) register_access_token = json.loads(response.text)['access_token'] print(json.dumps(response.json(), indent=4, sort_keys=True)) headers = { 'Content-Type': 'application/json', 'Authorization': "Bearer " + register_access_token } """ # Delete the client if it already exists print() print(f"Delete the client {client_id} if it exists, using the BEARER_TOKEN") print() print(baseURL, "client", client_id) response = requests.request("DELETE", f"{baseURL}/SASLogon/oauth/clients/{client_id}", headers=headers, verify=pem_path) print(json.dumps(response.json(), indent=4, sort_keys=True)) """ # Register the client using the BEARER_TOKEN print(f"\nRegister the client {client_id} using the BEARER_TOKEN\n") # create API call payload data payload='{"client_id": "' + client_id +'","client_secret": "'+ client_secret +'","scope": ["openid", "uaa.user"],"authorized_grant_types": ["authorization_code","refresh_token"],"redirect_uri": "urn:ietf:wg:oauth:2.0:oob","access_token_validity": "43199"}' # generate API call for register access token url = f"{baseURL}/SASLogon/oauth/clients" # REGISTER THE CLIENT process the results response = requests.request("POST", url, headers=headers, data=payload, verify=pem_path) print(json.dumps(response.json(), indent=4, sort_keys=True)) # Create an access token using the client and an authorization code print('\nCreate an access token using the client and an authorization code\n') # create the authorization url codeURL = baseURL + "/SASLogon/oauth/authorize?client_id=" + client_id + "&response_type=code" # encode the client string client_string = client_id + ":" + client_secret message_bytes = client_string.encode('ascii') base64_bytes = base64.b64encode(message_bytes) base64_message = base64_bytes.decode('ascii') # prompt with instructions and entry for auth code print(f"* Please visit the following site {codeURL}") print("* If provided a login prompt, add your SAS login credentials") print("* Once authenticated, you'll be redirected to an authorization screen, check all of the boxes that appear") print("* This will result in a short string of numbers and letters such as `VAxVFVEnKr`; this is your authorization code; copy the code") code = input("Please enter the authorization code you generated through the previous instructions, and then press Enter: ") # generate API call for access token url = f"{baseURL}/SASLogon/oauth/token#authorization_code" payload = "grant_type=authorization_code&code=" + code headers = { 'Accept': 'application/json', 'Content-Type': 'application/x-www-form-urlencoded', 'Authorization': "Basic " + base64_message } # process the results response = requests.request("POST", url, headers=headers, data=payload, verify=pem_path) access_token = json.loads(response.text)['access_token'] refresh_token = json.loads(response.text)['refresh_token'] print(json.dumps(response.json(), indent=4, sort_keys=True)) # Create directory #directory = os.getcwd() directory = 'api' if not os.path.exists("api/"): os.mkdir("api/") # Create access_token.txt file with open(directory + '/access_token.txt', 'w') as f: f.write(access_token) print('\nThe access token was stored for you as ' + directory + '/access_token.txt\n') # Create refresh_token.txt file with open(directory + '/refresh_token.txt', 'w') as f: f.write(refresh_token) print('\nThe refresh token was stored for you as ' + directory + '/refresh_token.txt\n') Steps in the "2023" method:   Get a BEARER_TOKEN to register (or delete) a client. Delete the client if it exists - commented out, as not relevant when running a first time. Register the client gel_app. Create an access token using the registered client and an authorization code. Visit a web-page, select the scopes and generate the authorization code. Paste the authorization code and get an access token and a refresh token.   Please note, you must remove statements that print the token from your production code. Those are there just for debugging.   Run the Program   In a Bash terminal on a Windows machine, you can run the program with the parameters you've set.   SASUSER=fill_in_here # SAS user SASPASS=fill_in_here # SAS user password CLIENTSECRET=fill_in_here # client secret # Certificate on a Windows machine and executable is python python get_token_2023.py https://my_sas_viya_url.sas.com gel_app $CLIENTSECRET $SASUSER $SASPASS 'C:\Users\myuser\Downloads\gelenv_trustedcerts.pem'   A SAS Viya certificate is used here, in the form of a .pem file. The .pem file was copied in the 'C:\Users\...\' folder.   In a Bash terminal on a Linux machine, the above statement would be:   SASUSER=fill_in_here # SAS user SASPASS=fill_in_here # SAS user password CLIENTSECRET=fill_in_here # client secret # Certificate on a Linux machine and executable is python3 python3 get_token_2023.py https://my_sas_viya_url.sas.com gel_app $CLIENTSECRET $SASUSER $SASPASS /home/my_user/.certs/gelenv_trustedcerts.pem   A SAS Viya certificate is used here, in the form of a .pem file. The .pem file is assumed to be present in the '/home/myuser/' folder.   Refresh Token   Access tokens have a 1-hour time-to-live (ttl) by default, so it's common to use a refresh token to generate a new access token. The refresh token itself has a 90-day ttl, while the authorization code is good for 30 minutes and single use. The access token generated can be transferred to other notebooks, programs and used for external API calls.   Get an Access Token with a Refresh Token   You can use a refresh token to get an access token when the latter expires.   # 1 Packages import requests, json, os, base64, sys # 2. Arguments print ("Number of arguments:", len(sys.argv), "arguments") #print ("Argument List:", str(sys.argv)) baseURL=str(sys.argv[1]) client_id=str(sys.argv[2]) client_secret=str(sys.argv[3]) pem_path=str(sys.argv[4]) print ("\nWe are going to use a certificate: ", pem_path) # encode client string client_string = client_id + ":" + client_secret message_bytes = client_string.encode('ascii') base64_bytes = base64.b64encode(message_bytes) base64_message = base64_bytes.decode('ascii') # 3. Construct Variables authUri="/SASLogon/oauth/token#refresh_token" print('Your SAS VIYA host is ', baseURL, '\n') print ('\nRefresh the access token by using the refresh token\n') url = baseURL + authUri # read the refresh token directory = 'api' print('\nRetrieving the saved refresh token\n') with open("api/refresh_token.txt", "r", encoding="UTF-8") as f: refresh_token = f.read() #print(refresh_token) payload = "grant_type=refresh_token&refresh_token=" + refresh_token headers = { 'Content-Type': 'application/x-www-form-urlencoded', 'Accept': 'application/json', 'Authorization': "Basic " + base64_message } response = requests.request("POST", url, headers=headers, data=payload, verify=pem_path) #print(response.text) # process the results response = requests.request("POST", url, headers=headers, data=payload, verify=pem_path) access_token = json.loads(response.text)['access_token'] print(json.dumps(response.json(), indent=4, sort_keys=True)) # Create access_token.txt file with open(directory + '/access_token.txt', 'w') as f: f.write(access_token) print('\nThe access token was stored for you as ' + directory + '/access_token.txt\n')   Please note, you must remove statements that print the token. Those are there just for debugging.   Run the Program   In a Bash terminal on a Windows machine, you can run the program with the parameters you've set.   Note that you no longer need the user and the password with the refresh token method. The client, the secret and the refresh token itself are enough to get you an access token. In all the subsequent posts, we will use the refresh token to get a new access token.   CLIENTSECRET=fill_in_here # client secret # Certificate on a Windows machine and executable is python python refresh_token_2023.py https://my_sas_viya_url.sas.com gel_app $CLIENTSECRET 'C:\Users\myuser\Downloads\gelenv_trustedcerts.pem'   In a Bash terminal on a Linux machine, you can run the program with the parameters you've set.   CLIENTSECRET=fill_in_here # client secret # Certificate on a Linux machine and executable is python3 python get_token_2023.py https://my_sas_viya_url.sas.com gel_app $CLIENTSECRET $SASUSER $SASPASS /home/my_user/.certs/gelenv_trustedcerts.pem   Conclusions   Learning how to harness the power of the SAS Information Catalog APIs can unlock a new level of efficiency and customization in managing your SAS Viya assets. By calling them from Python you can open the door to new integrations. This tutorial on acquiring an access token is the first step in that journey. Stay tuned for more in-depth discussions and tutorials in this series.   What to Read Next   Read the following post  in the series: Download Metadata and Metrics with SAS Information Catalog APIs from Python.   Acknowledgements   Thanks to my colleaque @joeFurbee  for all his work in this space.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email. ... View more

In a previous post, GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach, we discussed an agent using GPT-4, LangChain, and SASPY for data management in SAS Viya. This agent performed tasks like getting table info, column details, and table manipulation. Now, we'll discuss how to create such a smart agent.   The Big Picture   In simple terms, LangChain matches the user's intent with the best custom tool. This tool then interacts with SAS Viya.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   After this, the log is returned and a response is created by the large language model (LLM). If you haven't already, check out the video in the previous article, GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach, for more information.     Now let's look at what you need to build this custom agent.   Components   SAS Viya Environment   You will need a SAS Viya deployment. From your SAS Viya deployment you will need: The SAS Viya URL. A SAS username. The SAS user's password.   Azure OpenAI   You need to create a resource and deploy a model. To make a call successfully against Azure OpenAI, you need an endpoint, a key, and a deployment. Visual Studio Code   If you're a regular user of Visual Studio Code, great.   Python Requirements   Create a requirements.txt file that includes these Python packages:   saspy langchain langchain_openai pandas   Azure OpenAI Configuration   Create a 'config.py' file in a folder to hold the Azure OpenAI configuration.   azure_openai_endpoint =  'https://resource-name.openai.azure.com/' # your endpoint should look like https://YOUR_RESOURCE_NAME.openai.azure.com/ api_key = 'fill_in_here' # your key looks like 932e...a99 api_version = '2023-07-01-preview'   You'll refer back to this program later in the sastools_mem.py program using 'import config'.    Setup SASPY   We'll use SASPY to send programs from a Python program to a SAS Viya instance. We'll use SASPY in the custom SAS Viya tools that we'll create in the main Python program. Ajmal Farzam has already given a brief explanation of SASPY in his article Submit workloads to SAS Viya from Python. I've used his guidelines to create two files:   sascfg_personal.py   Create a sascfg_personal.py file that contains a profile, the SAS Viya URL and the name of the authkey, among other details.   SAS_config_names=['httpsviya'] SAS_config_options = {'lock_down': False,                       'verbose'  : True,                       'prompt'   : True                      } SAS_output_options = {'output' : 'html5'}       # not required unless changing any of the default httpsviya = {'ip'      : 'my_sas_viya_URL.sas.com',              'context' : 'SAS Studio compute context',              'authkey' : 'HTTP_Dev',              'options' : ["fullstimer", "memsize=4G"]              }   authinfo   SASPY is reading the credentials from an authinfo file:   Windows: Create a _authinfo file in C:\Users\myuser. The first word must match the authkey in the sascfg_personal.py file.   HTTP_Dev user SAS_USER password SAS_Pass_Value   Linux: you should create a .authinfo file in /home/myuser. Read more about it in the SASPY documentation / authinfo.   Optional: Create a Program to Test SASPY Connection   For example:   import sys sys.path import saspy # display active session sas = 'No active SAS session!' print(sas) # create _authinfo file in C:\Users\sbxbot if sas == 'No active SAS session!': sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') else: print ('There is a SAS session') print(sas) print(type(sas))   LangChain Custom Agent for SAS Viya Operations   The sastools_mem.py is our main Python program. It makes a LangChain custom agent that interacts with SAS tables in SAS Viya, carries out specific tasks, and provides answers based on those tasks.   # LangChain custom SAS Viya agent WITH memory ############################################## # Resource: https://python.langchain.com/docs/modules/agents/how_to/custom_agent # Load the LLM import os import urllib3 urllib3.disable_warnings ## A Large Language Model - Azure OpenAI from langchain_openai import AzureChatOpenAI ## Setting Azure OpenAI environment variables import config os.environ["OPENAI_API_TYPE"] = "azure" os.environ["OPENAI_API_KEY"] = config.api_key os.environ["AZURE_OPENAI_ENDPOINT"] = config.azure_openai_endpoint os.environ["OPENAI_API_VERSION"] = config.api_version ## GPT-4 as deployment llm = AzureChatOpenAI(deployment_name="gpt-4", temperature=0, max_tokens=2000) # Define tools # SAS - see https://communities.sas.com/t5/SAS-Communities-Library/Submit-workloads-to-SAS-Viya-from-Python/ta-p/807263 # non-SAS, such as get_word_length for control from langchain.agents import tool, Tool @tool def get_word_length(word: str) -> int: """Returns the length of a word. Useful when you need to calculate the length of a word or string. This will return the length as an integer.""" return len(word) def get_table_information(library_table: str) -> str: """Returns detailed table description for a SAS table. Accepts two inputs, table and library. Useful when you need to get detailed information about a SAS table or data set from a SAS library. This will return the columns, the number of rows, statistics about the table.""" # SASPY auth and start session import saspy sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') table = library_table.strip().split('.')[-1] library = library_table.strip().split('.')[0] sas_code = f"PROC CONTENTS DATA={library}.{table};run;quit;" sas_submit = sas.submit(sas_code) # Display results return (sas_submit['LOG']) def get_column_info_table(library_table: str) -> str: """Returns information about the columns in a SAS table. Useful when you need to get detailed information about a SAS table or data set from a SAS library. This will return the result of PROC CONTENTS on the table.""" # SASPY auth and start session import saspy table = library_table.split('.')[-1] library = library_table.split('.')[0] sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') table = sas.sasdata(table, library) # DESCRIBE TABLE return (table.columnInfo()) def append_table(library_table: str) -> str: """Returns an appended table from two SAS tables or data sets. This is useful when you need to append two tables from SAS libraries. This will create a SAS data set called NEW in the SASDM library.""" # SASPY auth and start session import saspy sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') base = library_table.strip().split(',')[0] appended = library_table.strip().split(',')[-1] sas_code = f"data sasdm.new; set {base};run; proc append base=sasdm.new data={appended} force; run; quit;" sas_submit = sas.submit(sas_code) # Display results return (sas_submit['LOG']) tools = [ Tool( name = "get_word_length", func = get_word_length, description = "Useful when you need to calculate the length of a word or string. This will return the length." ), Tool( name = "get_table_information", func = get_table_information, description = "Useful when you need to get detailed information about a SAS table or data set from a SAS library. This will return the columns, the number of rows, statistics about the table." ), Tool( name = "get_column_info_table", func = get_column_info_table, description = "Useful when you need to get detailed information about a SAS table, at a column level. This will return the columns, the number of rows, statistics about the table." ), Tool( name = "append_table", func = append_table, description = "Returns an appended table from two SAS tables or data sets. This is useful when you need to append two tables from SAS libraries. This will create a SAS data set called NEW in the SASDM library." ) ] # Create Prompt ## Now let us create the prompt. Because OpenAI Function Calling is finetuned for tool usage, we hardly need any instructions on how to reason, or how to output format. ## We will just have two input variables: input and agent_scratchpad. input should be a string containing the user objective. ## agent_scratchpad should be a sequence of messages that contains the previous agent tool invocations and the corresponding tool outputs. # Add Memory ## Add a place for memory variables to go in the prompt ## Keep track of the chat history ## First, let’s add a place for memory in the prompt. We do this by adding a placeholder for messages with the key "chat_history". Notice that we put this ABOVE the new user input (to follow the conversation flow). from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder MEMORY_KEY = "chat_history" prompt = ChatPromptTemplate.from_messages( [ ( "system", """You are a world class Data Engineer, who can do detailed research using SAS. Please make sure you complete the objective above with the following rules: 1/ Your job is to first breakdown the topic into relevant questions for understanding the topic in detail. You should have at max only 3 questions not more than that, in case there are more than 3 questions consider only the first 3 questions and ignore the others. 2/ You should use the get table information or get column info tools to get more information about a given topic. You are allowed to use these tools only 3 times in this process. 3/ Perform the operations demanded by the user, such as append, merge, etc. using the appropriate tools.""", ), MessagesPlaceholder(variable_name=MEMORY_KEY), ("user", "{input}"), MessagesPlaceholder(variable_name="agent_scratchpad"), ] ) # Bind tools to LLM ## How does the agent know what tools it can use? In this case we’re relying on OpenAI function calling LLMs, which take functions as a separate argument and have been specifically trained to know when to invoke those functions. ## To pass in our tools to the agent, we just need to format them to the OpenAI function format and pass them to our model. (By bind-ing the functions, we’re making sure that they’re passed in each time the model is invoked.) from langchain_community.tools.convert_to_openai import format_tool_to_openai_function llm_with_tools = llm.bind(functions=[format_tool_to_openai_function(t) for t in tools]) # Create the Agent ## Putting those pieces together, we can now create the agent. We will import two last utility functions: a component for formatting intermediate steps (agent action, tool output pairs) to input messages that can be sent to the model, and a component for converting the output message into an agent action/agent finish. from langchain.agents.format_scratchpad import format_to_openai_function_messages from langchain.agents.output_parsers import OpenAIFunctionsAgentOutputParser ## We can then set up a list to track the chat history from langchain_core.messages import AIMessage, HumanMessage chat_history = [] agent = ( { "input": lambda x: x["input"], "agent_scratchpad": lambda x: format_to_openai_function_messages( x["intermediate_steps"] ), "chat_history": lambda x: x["chat_history"], } | prompt | llm_with_tools | OpenAIFunctionsAgentOutputParser() ) from langchain.agents import AgentExecutor agent_executor = AgentExecutor(agent=agent, tools=tools, verbose=True) # ASK QUESTIONS ## Control q = 'How many letters in the word SASDM?' print('\nI will ask: ', q, '\n') result = agent_executor.invoke({"input": q, "chat_history": chat_history}) chat_history.extend( [ HumanMessage(content=q), AIMessage(content=result["output"]), ] ) q = 'Is that a real word?' print('\nI will ask: ', q, '\n') agent_executor.invoke({"input": q, "chat_history": chat_history}) ## SAS Viya q = 'Can you get column information for the SAS table PRDSAL2 from the SASHELP SAS library?' print('\nI will ask: ', q, '\n') result = agent_executor.invoke({"input": q, "chat_history": chat_history}) chat_history.extend( [ HumanMessage(content=q), AIMessage(content=result["output"]), ] ) q = 'Are there any columns containing date or period of time?' print('\nI will ask to test memory: ', q, '\n') agent_executor.invoke({"input": q, "chat_history": chat_history}) q = 'Append the table PRDSAL2 from SASHELP library to the table PRDSAL3 from SASHELP library' print('\nI will ask: ', q, '\n') result = agent_executor.invoke({"input": q, "chat_history": chat_history}) chat_history.extend( [ HumanMessage(content=q), AIMessage(content=result["output"]), ] ) q = 'How many rows again?' print('\nI will ask to test memory: ', q, '\n') agent_executor.invoke({"input": q, "chat_history": chat_history}) q = 'Provide detailed table description for table NEW from SASDM library' print('\nI will ask: ', q, '\n') result = agent_executor.invoke({"input": q, "chat_history": chat_history}) chat_history.extend( [ HumanMessage(content=q), AIMessage(content=result["output"]), ] ) q = 'In what library is stored that table?' print('\nI will ask to test memory: ', q, '\n') agent_executor.invoke({"input": q, "chat_history": chat_history})   Importing Libraries   Initially, the script imports necessary Python libraries and loads the Large Language Model (LLM) from LangChain. AzureChatOpenAI from the langchain_openai library is used as the LLM. The script sets up the necessary environment variables for Azure OpenAI using a config file.     import os import urllib3 from langchain_openai import AzureChatOpenAI import config ...   Defining Tools   The script defines several tools, which are Python functions to perform certain tasks. These tools help in working with SAS tables. They can fetch table information, fetch column information, and append tables.   from langchain.agents import tool, Tool @tool def get_word_length(word: str) -> int: """Returns the length of a word.""" ... def get_table_information(library_table: str) -> str: """Returns detailed table description for a SAS table.""" ... def get_column_info_table(library_table: str) -> str: """Returns information about the columns in a SAS table.""" ... def append_table(library_table: str) -> str: """Returns an appended table from two SAS tables or data sets. This is useful when you need to append two tables from SAS libraries. This will create a SAS data set called NEW in the SASDM library.""" # SASPY auth and start session import saspy sas = saspy.SASsession(cfgfile='sascfg_personal.py', cfgname = 'httpsviya') base = library_table.strip().split(',')[0] appended = library_table.strip().split(',')[-1] sas_code = f"data sasdm.new; set {base};run; proc append base=sasdm.new data={appended} force; run; quit;" sas_submit = sas.submit(sas_code) # Display results return (sas_submit['LOG'])   The append_table tool does the following: It imports SASPY. It starts a SAS session by linking to the SAS Studio compute context, which was defined in the sascfg_personal.py file. It parses the 'library_table' variable, whose value is given by the LLM. It executes the SAS code with the parsed arguments, in a SAS session SASPY creates.   Note: We specify the name of the table to be created, just to verify the results. The name can be another parameter, sure.   Creating the Agent   After defining the tools, the script creates an agent using the LangChain API. This agent uses the OpenAI GPT-4 model and is linked with the tools defined earlier. The agent gets a prompt template which guides it on how to interact with the user.   from langchain_community.tools.convert_to_openai import format_tool_to_openai_function ... from langchain.agents.format_scratchpad import format_to_openai_function_messages from langchain.agents.output_parsers import OpenAIFunctionsAgentOutputParser ... from langchain.agents import AgentExecutor ...   Running the Agent   Finally, the script runs the agent by asking a series of questions. There are SAS and non-SAS questions. The latter are used for control:   How many letters in the word SASDM? (non-SAS) Is that a real word? (non-SAS) Can you get column information for the SAS table PRDSAL2 from the SASHELP SAS library? Are there any columns containing date or period of time? Append the table PRDSAL2 from SASHELP library to the table PRDSAL3 from SASHELP library How many rows again? Provide detailed table description for table NEW from SASDM library In what library is the table stored?   The function agent_executor.invoke() is used to communicate with the agent and obtain its responses. The chat history is stored and used as context for future questions.   from langchain_core.messages import AIMessage, HumanMessage ... q = 'Append the table PRDSAL2 from SASHELP library to the table PRDSAL3 from SASHELP library' print('\nI will ask: ', q, '\n') ... result = agent_executor.invoke({"input": q, "chat_history": chat_history}) ... q = 'How many rows again?' agent_executor.invoke({"input": q, "chat_history": chat_history})   Summary   In short, this Python script sets up a custom LangChain agent. This agent can handle detailed operations on SAS tables and respond to user questions based on those operations.   Run the Python Program   In a Bash terminal type:   pip install -r requirements.txt python sastools_mem.py   Interpreting the Execution Logs   This section provides an extract of the full log generated by the running Python program:   I will ask: Can you get column information for the SAS table PRDSAL2 from the SASHELP SAS library? > Entering new AgentExecutor chain... Invoking: `get_column_info_table` with `SASHELP.PRDSAL2` SAS server started using Context SAS Studio compute context with SESSION_ID=4768a27c-0826-496e-8693-c67e26bc5aab-ses0000 Member Num Variable Type Len Pos Format Informat Label 0 SASHELP.PRDSAL2 4.0 ACTUAL Num 8.0 0.0 DOLLAR12.2 NaN Actual Sales 1 SASHELP.PRDSAL2 1.0 COUNTRY Char 10.0 48.0 $CHAR10. NaN Country 2 SASHELP.PRDSAL2 3.0 COUNTY Char 20.0 80.0 $CHAR20. NaN County 3 SASHELP.PRDSAL2 10.0 MONTH Num 8.0 32.0 MONNAME3. NaN Month 4 SASHELP.PRDSAL2 11.0 MONYR Num 8.0 40.0 MONYY. MONYY. Month/Year 5 SASHELP.PRDSAL2 5.0 PREDICT Num 8.0 8.0 DOLLAR12.2 NaN Predicted Sales 6 SASHELP.PRDSAL2 6.0 PRODTYPE Char 10.0 100.0 $CHAR10. NaN Product Type 7 SASHELP.PRDSAL2 7.0 PRODUCT Char 10.0 110.0 $CHAR10. NaN Product 8 SASHELP.PRDSAL2 9.0 QUARTER Num 8.0 24.0 8. NaN Quarter 9 SASHELP.PRDSAL2 2.0 STATE Char 22.0 58.0 $CHAR22. NaN State/Province 10 SASHELP.PRDSAL2 8.0 YEAR Num 8.0 16.0 4. NaN YearThe SAS table PRDSAL2 from the SASHELP library has the following columns: 1. ACTUAL: This is a numeric column with a length of 8. It uses the DOLLAR12.2 format and represents the actual sales. 2. COUNTRY: This is a character column with a length of 10. It uses the $CHAR10. format and represents the country. 3. COUNTY: This is a character column with a length of 20. It uses the $CHAR20. format and represents the county. 4. MONTH: This is a numeric column with a length of 8. It uses the MONNAME3. format and represents the month. 5. MONYR: This is a numeric column with a length of 8. It uses the MONYY. format and represents the month/year. 6. PREDICT: This is a numeric column with a length of 8. It uses the DOLLAR12.2 format and represents the predicted sales. 7. PRODTYPE: This is a character column with a length of 10. It uses the $CHAR10. format and represents the product type. 8. PRODUCT: This is a character column with a length of 10. It uses the $CHAR10. format and represents the product. 9. QUARTER: This is a numeric column with a length of 8. It uses the 8. format and represents the quarter. 10. STATE: This is a character column with a length of 22. It uses the $CHAR22. format and represents the state/province. 11. YEAR: This is a numeric column with a length of 8. It uses the 4. format and represents the year. > Finished chain. I will ask to test memory: Are there any columns containing date or period of time? > Entering new AgentExecutor chain... Yes, there are columns containing date or period of time in the PRDSAL2 table from the SASHELP library. These columns are: 1. MONTH: This is a numeric column with a length of 8. It uses the MONNAME3. format and represents the month. 2. MONYR: This is a numeric column with a length of 8. It uses the MONYY. format and represents the month/year. 3. QUARTER: This is a numeric column with a length of 8. It uses the 8. format and represents the quarter. 4. YEAR: This is a numeric column with a length of 8. It uses the 4. format and represents the year. I will ask: Append the table PRDSAL2 from SASHELP library to the table PRDSAL3 from SASHELP library > Entering new AgentExecutor chain... Invoking: `append_table` with `SASHELP.PRDSAL2, SASHELP.PRDSAL3` SAS server started using Context SAS Studio compute context with SESSION_ID=8ecc570f-0f01-4153-9e32-59da3d37d7ab-ses0000 12 ods listing close;ods html5 (id=saspy_internal) options(bitmap_mode='inline') device=svg style=HTMLBlue; ods graphics on / 12 ! outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: sashtml.htm 13 14 data sasdm.new; set SASHELP.PRDSAL2;run; NOTE: There were 23040 observations read from the data set SASHELP.PRDSAL2. NOTE: The data set SASDM.NEW has 23040 observations and 11 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 626.15k OS Memory 17764.00k Timestamp 01/23/2024 01:51:06 AM Step Count 3 Switch Count 0 Page Faults 0 Page Reclaims 168 Page Swaps 0 Voluntary Context Switches 76 Involuntary Context Switches 0 Block Input Operations 32 Block Output Operations 5512 14 ! proc append base=sasdm.new data= SASHELP.PRDSAL3 force; run; NOTE: Appending SASHELP.PRDSAL3 to SASDM.NEW. WARNING: Variable DATE was not found on BASE file. The variable will not be added to the BASE file. WARNING: Variable MONYR was not found on DATA file. NOTE: FORCE is specified, so dropping/truncating will occur. NOTE: There were 11520 observations read from the data set SASHELP.PRDSAL3. NOTE: 11520 observations added. NOTE: The data set SASDM.NEW has 34560 observations and 11 variables. NOTE: PROCEDURE APPEND used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 715.50k OS Memory 18020.00k Timestamp 01/23/2024 01:51:06 AM Step Count 4 Switch Count 0 Page Faults 0 Page Reclaims 101 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 288 Block Output Operations 2960 14 ! quit; 15 ods html5 (id=saspy_internal) close;ods listing; 16 The tables PRDSAL2 and PRDSAL3 from the SASHELP library have been successfully appended. The new table is stored in the SASDM library and is named "NEW". It contains 34560 observations and 11 variables. Please note that the variable "DATE" was not found in the PRDSAL2 table and the variable "MONYR" was not found in the PRDSAL3 table. > Finished chain. I will ask to test memory: How many rows again? > Entering new AgentExecutor chain... The new appended table contains 34560 rows. > Finished chain. I will ask: Provide detailed table description for table NEW from SASDM library > Entering new AgentExecutor chain... Invoking: `get_table_information` with `SASDM.NEW` SAS server started using Context SAS Studio compute context with SESSION_ID=7b7f0494-181a-496c-a2f3-35cb4605c742-ses0000 12 ods listing close;ods html5 (id=saspy_internal) options(bitmap_mode='inline') device=svg style=HTMLBlue; ods graphics on / 12 ! outputfmt=png; NOTE: Writing HTML5(SASPY_INTERNAL) Body file: sashtml.htm 13 14 PROC CONTENTS DATA=SASDM.NEW;run; NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.03 seconds user cpu time 0.03 seconds system cpu time 0.00 seconds memory 1937.50k OS Memory 19300.00k Timestamp 01/23/2024 01:51:44 AM Step Count 3 Switch Count 0 Page Faults 0 Page Reclaims 671 Page Swaps 0 Voluntary Context Switches 3 Involuntary Context Switches 3 Block Input Operations 0 Block Output Operations 8 14 ! quit; 15 ods html5 (id=saspy_internal) close;ods listing; 16 The table NEW from the SASDM library has the following details: 1. ACTUAL: This is a numeric column with a length of 8. It uses the DOLLAR12.2 format and represents the actual sales. 2. COUNTRY: This is a character column with a length of 10. It uses the $CHAR10. format and represents the country. 3. COUNTY: This is a character column with a length of 20. It uses the $CHAR20. format and represents the county. 4. MONTH: This is a numeric column with a length of 8. It uses the MONNAME3. format and represents the month. 5. MONYR: This is a numeric column with a length of 8. It uses the MONYY. format and represents the month/year. 6. PREDICT: This is a numeric column with a length of 8. It uses the DOLLAR12.2 format and represents the predicted sales. 7. PRODTYPE: This is a character column with a length of 10. It uses the $CHAR10. format and represents the product type. 8. PRODUCT: This is a character column with a length of 10. It uses the $CHAR10. format and represents the product. 9. QUARTER: This is a numeric column with a length of 8. It uses the 8. format and represents the quarter. 10. STATE: This is a character column with a length of 22. It uses the $CHAR22. format and represents the state/province. 11. YEAR: This is a numeric column with a length of 8. It uses the 4. format and represents the year. The table has a total of 34560 observations. > Finished chain. I will ask to test memory: In what library is stored that table? > Entering new AgentExecutor chain... The table "NEW" is stored in the SASDM library. > Finished chain. SAS server terminated   You can watch the video in GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach to follow the execution logs.   Appending Tables   The program is able to append the table PRDSAL2 from the SASHELP library to the table PRDSAL3 from the SASHELP library. The operation is successful and the new table, named "NEW", is stored in the SASDM library. It has 34560 observations and 11 variables. **Invoking: append_table with SASHELP.PRDSAL2, SASHELP.PRDSAL3**   The tables PRDSAL2 and PRDSAL3 from the SASHELP library have been successfully appended. The new table is stored in the SASDM library and is named "NEW". It contains 34560 observations and 11 variables. Please note that the variable "DATE" was not found in the PRDSAL2 table and the variable "MONYR" was not found in the PRDSAL3 table. > Finished chain. I will ask to test memory: How many rows again? > Entering new AgentExecutor chain... The new appended table contains 34560 rows. > Finished chain.   Conclusions   The implementation of a custom SAS Viya agent in LangChain is successful, demonstrating the potential of using LLMs and specific tools to create intelligent, interactive agents. The next steps could involve expanding the range of tools available to the agent and optimizing the agent’s memory component to handle more complex interactions.   Additional Resources   SASPY: Submit workloads to SAS Viya from Python. GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach. Conversing with Data: Turning Queries into Conversations with SAS Viya, Azure OpenAI and LangChain. Exploring LangChain and Azure OpenAI’s Ability to Write SQL and Join Tables To Answer Questions.   What To Read Next   Want to view a demonstration of this custom agent? View the video in GPT-4 Assisted Data Management in SAS Viya: A Custom LangChain Agent Approach.    Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email. ... View more

This post explores an alternative approach to data management in SAS Viya using a custom LangChain agent. This agent enables users to engage in a conversational interface with SAS Viya, bypassing the need for complex programming. By leveraging the power of large language models (LLMs) and SASPY, the agent offers a variety of data management tasks that include fetching table information, retrieving column details, and manipulating tables. What's more, the agent's built-in memory feature allows for the context-aware handling of user queries and the reusability of responses for subsequent questions. This approach opens the doors to an era of enhanced user interaction and automation in data management tasks, making it a valuable tool for data engineers and analysts alike.   Earlier, I explored employing LangChain with structured data:   From SAS data sets in Conversing with Data: Turning Queries into Conversations with SAS Viya, Azure OpenAI and LangChain. From Azure SQL databases in Exploring LangChain and Azure OpenAI’s Ability to Write SQL and Join Tables To Answer Questions.   I was excited by the possibilities. I was equally disappointed that there was no LangChain agent for SAS Viya.   Therefore, I wrote one. The post explains the result.   The Big Picture   Watch the following video. The code executed is a Python program that defines a custom agent for interacting with SAS Viya. It leverages the LangChain Python library, Azure OpenAI, and SASPY to interact with SAS libraries and perform various operations such as fetching table information, getting column information, and appending tables.   The Finer Picture   The custom agent is primarily designed to interact with a Large Language Model (LLM), specifically, the GPT-4 model deployed on Azure OpenAI. It utilizes a set of custom defined tools to retrieve and manipulate data in SAS libraries. These tools use SASPY to connect to a SAS Viya instance, start a session and execute SAS code. It also features a memory component to remember previous interactions, which aids in maintaining context across a series of questions or commands.   Key Components   The program consists of several key components:   LLM Setup: The LLM is set up using AzureChatOpenAI from the langchain_openai Python module. The environment variables for Azure OpenAI are set, and the GPT-4 model is selected for deployment. Tool Definitions: A series of functions (tools) are defined for various tasks, such as fetching table and column info, or appending tables in SAS. These tools are then bound to the LLM. The LLM has to use them. The SAS Viya tools were created using the approach described on the LangChain Custom Agent page. I am using the getting word length tool as a control function, to sepaarate SAS from non-SAS tools. Chat Prompt Creation: A ChatPromptTemplate is created, which sets up the structure of the conversation, including the system prompts, user inputs, agent's scratchpad, and chat history. Agent Creation: An agent is created that takes user input and chat history, applies the prompt template, executes the LLM with the tools, and parses the output. Agent Execution: The agent is executed on a series of questions or commands, and the responses are stored in the chat history to maintain context.   How The Components Work Together   The agent operates by taking a user question or command, applying the prompt template, and executing the LLM with the relevant tools. The output is parsed and returned to the user. The chat history maintains a record of previous interactions, which allows the agent to refer back to earlier responses, ensuring continuity and context across multiple user inputs.   The Importance of the Approach   This approach is critical for creating intelligent, interactive agents that can handle complex tasks and maintain context across multiple interactions. By leveraging LLMs, the agent can understand and respond to a wide range of queries or commands. The use of specific tools enables the agent to interact with SAS libraries, making it a useful tool for data engineers and analysts working with SAS.   What the Program Brings to the Table   This program demonstrates how to create a powerful, interactive custom agent for SAS Viya capable of performing complex tasks and maintaining context across multiple interactions. It showcases the potential of LLMs in creating intelligent agents and the use of SASPY in interacting with SAS Viya. SASPY is just an example. The same approach could be employed with SWAT, SASCTL, or plain SAS Viya REST API.   Conclusions and Next Steps   The implementation of the custom SAS Viya agent in LangChain is successful, demonstrating the potential of using LLMs and specific tools to create intelligent, interactive agents. The next steps could involve expanding the range of tools available to the agent and optimizing the agent's memory component to handle more complex interactions.   My Own Opinion About the Approach   Personally, I believe this approach represents an exciting advancement in the field of data management. By using AI and natural language processing to make SAS Viya more accessible to non-technical users, making it easier to use, we're opening up new possibilities for data analysis and manipulation. I'm excited to see where this technology takes us in the future.   In a next post, I will describe in more detail the Python program and the different prerequisites.    What To Read Next   Want to understand what it takes to build a custom agent? Read the post How to Create Your Custom LangChain Agent for SAS Viya. Acknowledgements   Thanks to @AjmalFarzam  for teaching me SASPY in 15 minutes with his Submit workloads to SAS Viya from Python.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email.     ... View more

In the realm of Large Language Models (LLMs), the ability to chat with unstructured text data has been well-established. But what if we could extend this capability to interact with structured data? What if an AI could write SQL queries and even perform table joins to answer your data related questions? This is not a mere SQL fiction. With LangChain, a framework for developing applications powered by language models, and Azure OpenAI, this is reality.   To illustrate this, consider the structured data stored in an Azure SQL database. This database houses two tables: the 'Titanic' dataset from Kaggle and an invented 'Social' table filled with additional information. These tables can be joined using a common key.   LangChain, with its SQL agent, can connect to the database and execute actions. Combined with the GPT-4 model deployed in an Azure OpenAI resource, it can answer questions by executing SQL queries, even figuring out how to join the two tables if necessary.   To demonstrate, consider a Python program run from Visual Studio Code. This program sends questions to the LangChain agent and waits for an answer. The objective is to determine if the LangChain SQL agent can join two tables to retrieve the correct answer.   Sounds like SQL-Fiction? Watch the video:     The chain of thought triggered by the question: "What is the Name of the passenger that has a Middle Status and has survived?"     The Python Script """ This script connects to a SQL database and runs a few basic queries. It also initializes an Agent for chat interaction with the database. """ import os from sqlalchemy import create_engine from sqlalchemy.engine.url import URL from langchain.sql_database import SQLDatabase from langchain.chat_models import AzureChatOpenAI from langchain.agents.agent_toolkits import SQLDatabaseToolkit from langchain.agents import AgentExecutor from langchain.agents.agent_types import AgentType from langchain.agents import create_sql_agent import config # Set up database connection parameters username = "myuser" os.environ["SQL_SERVER_USERNAME"] = username server_short = "mysqlsrv" os.environ["SQL_SERVER"] = server_short server = "mysqlsrv.database.windows.net" os.environ["SQL_SERVER_ENDPOINT"] = server password = "*********" os.environ["SQL_SERVER_PASSWORD"] = password database ="geldb" os.environ["SQL_SERVER_DATABASE"] = database driver = "{ODBC Driver 18 for SQL Server}" # Set up SQLAlchemy connection db_config = { 'drivername': 'mssql+pyodbc', 'username': os.environ["SQL_SERVER_USERNAME"] + '@' + os.environ["SQL_SERVER"], 'password': os.environ["SQL_SERVER_PASSWORD"], 'host': os.environ["SQL_SERVER_ENDPOINT"], 'port': 1433, 'database': os.environ["SQL_SERVER_DATABASE"], 'query': {'driver': 'ODBC Driver 18 for SQL Server'} } db_url = URL.create(**db_config) db = SQLDatabase.from_uri(db_url) # Set up Azure OpenAI os.environ["OPENAI_API_TYPE"] = "azure" os.environ["OPENAI_API_KEY"] = config.api_key os.environ["OPENAI_API_BASE"] = config.api_base os.environ["OPENAI_API_VERSION"] = config.api_version llm = AzureChatOpenAI(deployment_name="gpt-4", temperature=0, max_tokens=4000) # Set up SQL toolkit for LangChain Agent toolkit = SQLDatabaseToolkit(db=db, llm=llm) # Initialize and run the Agent agent_executor = create_sql_agent( llm=llm, toolkit=toolkit, verbose=True, agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION, ) # Ask Questions agent_executor.run("how many tables can be joined? by what key?") # ... Add more questions here ...   In this script, several operations are performed: Connection credentials for an Azure SQL database are set up. A connection is established using SQLAlchemy, a SQL toolkit and Object-Relational Mapping (ORM) system for Python. An Azure OpenAI connection to a GPT-4 deployment is set up, defining the LLM in the toolkit. A SQLDatabaseToolkit is used to allow the LLM to interact with the database. An AgentExecutor is created to execute the SQL queries. Questions are sent. A chain of actions, observations, thoughts and more actions is initiated until a final answer. Prerequisites: An Azure subscription. An Azure SQL DB deployed, and data loaded in the SQL DB. Access to Azure OpenAI. An Azure OpenAI resource with a model deployed. The demo used a gpt-4model. Python (version 3.7.1 or later). LangChain and SQLAlchemy libraries. ODBC Driver for SQL Server. Conclusions   Overall   The results are impressive. The LangChain agent successfully identifies a common key and joins two tables to retrieve the required information. The accuracy of the results depends on the question asked and the keywords used. For example, “combine” which semantically is remarkably similar to “join” produced the best result.   Column Selection: Metadata Is Important   The SQL agent utilizes table metadata to determine which columns can be used in a join, thereby underscoring the importance of metadata and data quality. As the LLM leverages the database schema, table and column names, data types, and frequency distributions to make a column selection, the metadata becomes crucial. I think in the future, we will be forced to choose well the column names, the descriptions, if we want to use AI agents to probe the data.   Code Generation: Brute-Force That Works   When it comes to code generation, the LangChain agent prompts the LLM to create SQL queries. These queries are then tested against the database until a successful query is achieved. Even in case of a SQL syntax error, the agent feeds the error back into the LLM, which then refines the SQL query, exemplifying an effective 'brute-force' approach.   Dangerous Inferences: Taking Shortcuts   It seems that, whether human or machine, the tendency to take shortcuts to avoid effort or mental fatigue is a shared trait. If an AI learnt from code thrown at it, it also learnt to take shortcuts, just like we do when we program. Interestingly, the LLM is capable of inferring that two columns contain similar information, even if the data types are different. The inference was incorrect, though. Unless you know the data and you test these inferences, you will not know you have been given the wrong answer.   In conclusion, LangChain and Azure OpenAI have shown remarkable capabilities in interacting with structured data, writing SQL queries, and performing table joins. As we move forward, the importance of metadata, data quality and the ability of humans to detect logic errors, will continue to play a significant role in achieving accurate results.   References My inspiration comes from Valentina Alto's post, Building your own DB Copilot for Azure SQL with Azure OpenAI GPT-4.   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email. ... View more

Imagine a world where you could discuss with your data as if it were a colleague. This might sound like a tech utopia, but with the power of SAS Viya, Azure OpenAI, Azure AI Speech service, LangChain, and Python programming, this is rapidly becoming a reality.     Demo One: Turning Queries into Conversations Have you ever wished you could just ask your data a question in plain English? Well, now you can.     Using the SASHELP.CARS data set as a source of data: You formulate a question using your voice. The voice is transformed into a text prompt. LangChain manages the prompt. A “thought” is formed. This “thought” then morphs into an actionable data query. The query further transforms into one or several observations. In some cases, data types are even converted on the fly from string to float, and unnecessary symbols like commas or dollar signs are removed.   Demo Two: Data Speaks Your Language English is not the only language in which you can formulate your queries. With the power of LangChain, you can also interact with your data in a language you speak, be it French, Dutch, Mandarin, Romanian or any other.     Text-to-Text and Speech-to-Speech: Two Sides of the Same Coin Text-to-text interaction, powered by Large Language Models (LLMs), has shown tremendous capabilities in handling unstructured data. When it comes to structured data, LangChain enters the fray, making it a breeze to interact with data.     Speech-to-speech interaction is another step forward. Here, your voice is captured and converted into text, and the generated response is converted back into speech.   SAS Studio: Your Data Interaction Playground When real-time interaction isn't a requirement, you can opt for a straightforward text-to-text approach. Within SAS Studio, you can call upon LangChain and Azure OpenAI with the help of PROC PYTHON.       Essential Components For text-to-text interaction, you'll need: An Azure subscription. Access to Azure OpenAI. An Azure OpenAI resource with a model deployed. The demo used a text-davinci-003 model. A data source. Python (version 3.7.1 or later). LangChain and Azure OpenAI libraries. When you are calling the Python program from SAS Studio, you must have: Python configured in SAS Viya. Python libraries pandas, openai and langchain installed. For speech-to-speech interaction, additional requirements include: Visual Studio Code with Python (version 3.7.1 or later). Python's azure-cognitiveservices-speech library. A Speech resource in your Azure subscription. Microsoft Visual C++ Redistributable for Visual Studio 2015, 2017, 2019, and 2022. Conclusion The amalgamation of SAS Viya, Azure OpenAI, LangChain, and Python programming is opening new dimensions in data interaction. It's not just about querying anymore; it's about conversing, transforming the way we engage with our data.   Digging Deeper In Valentina's blog, to process a SQL DB table, she loaded it in a Pandas DataFrame. I applied the same concept to a SAS data set, using PROC PYTHON to load it in a DataFrame.   I am fully aware that this approach might not be suitable for tens of millions of rows. However, you could construct aggregate data sets, first. The approach was quite common in Business Intelligence or Data Warehouse projects. Then, load those aggregates in a DataFrame.   However, in the first two demos, I had to first export the SASHELP.CARS data set to a CSV file and load it into a DataFrame, on my PC. This was due to the lack of a LangChain SAS agent and toolkit for direct SAS data access, and the necessity for my Python installation to access a microphone and speaker for speech-to-text and text-to-speech capabilities.   Despite this, LangChain can be employed in the Python setup associated with SAS Viya for text-to-text conversations. You can see this in action in the SAS Studio demo.   Code Explanation The code used in the speech-to-speech approach.     Text-to-Text SAS Program You can run the following program in SAS Studio to interact with Azure OpenAI and LangChain:   filename resp clear; /* This program uses Azure OpenAI to interact with a pandas DataFrame loaded from a SAS table. It sets up a LangChain agent that can answer queries about the data in the DataFrame. Functions: ask(query): Prints a query and the agent's answer to the query. */ proc python; submit; import pandas as pd from langchain.llms import AzureOpenAI import openai import os import config from langchain.agents import create_pandas_dataframe_agent df = pd.read_sas("/opt/sas/viya/home/SASFoundation/sashelp/cars.sas7bdat") os.chdir('folder_path_where_config.py_is_stored') # config.py contains the API details: keys, version, etc. os.environ["OPENAI_API_TYPE"] = "azure" os.environ["OPENAI_API_KEY"] = config.api_key os.environ["OPENAI_API_BASE"] = config.api_base os.environ["OPENAI_API_VERSION"] = config.api_version llm = AzureOpenAI(deployment_name=config.api_model, model_name=config.api_model) agent = create_pandas_dataframe_agent(llm, df, verbose=True) def ask(query): print('Q: ', query) print('Final Answer: ', agent.run(query)) query = 'list the top 5 car make model pairs with most horsepower.' ask(query) endsubmit; run;   Speech-to-Speech Python Program """ This script performs speech recognition and synthesis using Azure OpenAI and Azure Speech Service. It loads a CSV file into a pandas DataFrame and sets up an agent to interact with the data using Azure OpenAI. The script listens to user voice input, converts it to text, sends the text to Azure OpenAI, and synthesizes the OpenAI's response back to speech. Functions: ask_langchain(query): Sends a query to Azure OpenAI, gets the response, and synthesizes the response to speech. chat_with_open_ai(): Continuously listens for speech input, recognizes the speech, sends it to Azure OpenAI, and synthesizes the OpenAI's response back to speech """ import pandas as pd import openai import os import config from langchain.llms import AzureOpenAI from langchain.agents import create_pandas_dataframe_agent import azure.cognitiveservices.speech as speechsdk df = pd.read_csv("cars.csv") os.environ["OPENAI_API_TYPE"] = "azure" os.environ["OPENAI_API_KEY"] = config.api_key os.environ["OPENAI_API_BASE"] = config.api_base os.environ["OPENAI_API_VERSION"] = config.api_version os.environ["SPEECH_KEY"] = config.speech_key os.environ["SPEECH_REGION"] = config.speech_region llm = AzureOpenAI(deployment_name=config.api_model, model_name=config.api_model) agent = create_pandas_dataframe_agent(llm, df, verbose=True) speech_config = speechsdk.SpeechConfig(subscription=os.environ.get('SPEECH_KEY'), region=os.environ.get('SPEECH_REGION')) audio_output_config = speechsdk.audio.AudioOutputConfig(use_default_speaker=True) audio_config = speechsdk.audio.AudioConfig(use_default_microphone=True) speech_config.speech_recognition_language="en-US" speech_config.speech_synthesis_voice_name='en-US-JennyMultilingualNeural' speech_recognizer = speechsdk.SpeechRecognizer(speech_config=speech_config, audio_config=audio_output_config) speech_synthesizer = speechsdk.SpeechSynthesizer(speech_config=speech_config, audio_config=audio_config) def ask_langchain(query): text = agent.run(query) speech_synthesis_result = speech_synthesizer.speak_text_async(text).get() if speech_synthesis_result.reason == speechsdk.ResultReason.SynthesizingAudioCompleted: print("Speech synthesized to speaker for text [{}]".format(text)) if speech_synthesis_result.reason == speechsdk.ResultReason.Canceled: cancellation_details = speech_synthesis_result.cancellation_details print("Speech synthesis canceled: {}".format(cancellation_details.reason)) if cancellation_details.reason == speechsdk.CancellationReason.Error: print("Error details: {}".format(cancellation_details.error_details)) def chat_with_open_ai(): while True: print("Azure OpenAI is listening. Say 'Stop' or press Ctrl-Z to end the conversation.") try: speech_recognition_result = speech_recognizer.recognize_once_async().get() if speech_recognition_result.reason == speechsdk.ResultReason.RecognizedSpeech: if speech_recognition_result.text == "Stop.": print("Conversation ended.") break print("Recognized speech: {}".format(speech_recognition_result.text)) ask_langchain(speech_recognition_result.text) elif speech_recognition_result.reason == speechsdk.ResultReason.NoMatch: print("No speech could be recognized: {}".format(speech_recognition_result.no_match_details)) break elif speech_recognition_result.reason == speechsdk.ResultReason.Canceled: cancellation_details = speech_recognition_result.cancellation_details print("Speech Recognition canceled: {}".format(cancellation_details.reason)) if cancellation_details.reason == speechsdk.CancellationReason.Error: print("Error details: {}".format(cancellation_details.error_details)) print("Did you set the speech resource key and region values?") except EOFError: break try: chat_with_open_ai() except Exception as err: print("Encountered exception. {}".format(err))   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about having conversations with your data. If you wish to get more information, please write me an email. ... View more

Have you wondered how to run, in batch mode, a SAS Studio flow? This post provides you the latest approach and a few useful scripts. Since the Long-Term Support (LTS) release 2023.03 (May 2023), you can use the deploy - redeploy options available in SAS Studio. These options allow users to have the control over the code deployed. We can say SAS Studio flows execution has been made DevOps ready.   The present blog overrides an older post How to Run a SAS Studio Flow in Batch. If your sole purpose is to execute a SAS Studio flow in batch, it is no longer necessary to generate the code from a flow, as described in How To Get the Code From a SAS Studio Flow and Why It Matters or How to Get the Code From a SAS Studio Flow Using Python. Unless you have another use case that justifies it, for example versioning the code, altering it, "transporting" it, etc.   Deploy as a Job - Execute - Change - Redeploy   It's easy, watch the following video and you will see why.     Execute the Job   To execute a job, in batch, you can use the SAS Viya CLI.   Authenticate to SAS Viya and Get an Access Token   # Set Variables # SAS Viya namespace - optional, but used as a prefix elsewhere export current_namespace=gelenv export SAS_CLI_PROFILE=${current_namespace} # SAS Viya PEM file for TLS auth export SSL_CERT_FILE=~/.certs/${current_namespace}_trustedcerts.pem # user and pass USER=myuser@mytenant.com PASS=mypass # SAS Viya URL export INGRESS_URL=https://fill_in_here # SAS Viya CLI install location clidir=/opt/sas/viya/home/bin # Create a SAS Viya CLI Profile $clidir/sas-viya --profile ${SAS_CLI_PROFILE} profile set-endpoint ${INGRESS_URL} $clidir/sas-viya --profile ${SAS_CLI_PROFILE} profile toggle-color on $clidir/sas-viya --profile ${SAS_CLI_PROFILE} profile set-output fulljson # Show profile $clidir/sas-viya --profile ${SAS_CLI_PROFILE} profile show # Login in $clidir/sas-viya --profile ${SAS_CLI_PROFILE} auth login -user $USER -password $PASS unset PASS # Login succeeded. Token saved.   There are more elegant and secure approaches to managing credentials. Read further, to find out.   List the Existing Jobs   Filter by the name of the job, then retrieve the id in a variable jid.   # install jq if needed sudo apt install jq -y jid=$($clidir/sas-viya --profile ${SAS_CLI_PROFILE} --output json job requests list --filter 'eq(name,'Flow')' | jq -r '.items[]["id"]') echo "The SAS object is wrapped in this job request: $jid"   Execute the Job Request   With job requests execute, you are executing the latest version of the deployed code from your SAS Studio flow.   $clidir/sas-viya --profile ${SAS_CLI_PROFILE} job requests execute --id $jid   You must have the SAS Viya CLI installed on a machine where you will execute the commands. The CLI version 1.21.17 has been used in the examples that follow. For a complete plug-in list, see Command-Line Interface: Plug-Ins. The installation instructions can be found here: SAS® Viya®: Using the Command-Line Interface.   Managing Change   What happens if you change something in a SAS Studio flow? Save it. Redeploy it. "That's all, folks!"   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Execution Results and Logs   After posting, I received a few queries: "What about the logs, the status, the execution results? Is there a way to obtain them?"   I decided to do some extra investigations. The conclusions:   The SAS® Viya® Platform Administration documentation specifies that as of 2023.09 (September 2023) stable version:   The --wait-results option is available as of release 2.6.44 of the job CLI plug-in. You can use this option with the job requests execute and job flows execute commands.   The option enables the job CLI plug-in to wait for the results from the program associated with the job request or job flow. The CLI polls the program until the CLI receives the program results. The results are written to stdout. For an example of using this option, see Capture Results from a Program or Command in SAS Viya Platform: Jobs and Flows.       I could not find an option to get the full log, just like we do when we execute using the batch submit-pgm command.   Think Security   Avoid hardcoding credentials in your code files. You can use an: An .authinfo file in conjunction with the pyviya tools. See Gerry's posts: Introducing the GEL pyviyatools. If you are using SAS Viya on Azure, use Azure Key Vault to store your secrets.   Conclusions   To execute in batch SAS Studio flows, from SAS Viya 2023.03 or later, consider using the sas-viya job requests CLI and the deploy, redeploy flow SAS Studio options.   Resources   For more information, see the following information: SAS Documentation: Deploying and Redeploying a Flow or Program as a Job. How to Run SAS Programs with Parameters in Batch in SAS Viya. Run SAS Programs in Batch in Viya 4. Introducing the GEL pyviyatools.   Thank you for your time reading this post. If you liked the post, give it a thumbs up. Please comment and tell us what you think about running flows in batch. If you wish to get more information, please write me an email.   ... View more

A Pipeline might be the door to the DevOps world. To enter this world, from SAS Viya, you need a key to the door. The key is a self-hosted agent for SAS Viya. A self-hosted agent can be a Docker container. The Docker container agent has several advantages compared to a Virtual Machine. Would you like to know what these advantages are? Or how to create a Docker agent for SAS Viya? Read the post to find out more. SAS Viya and Azure DevOps To integrate SAS Viya with Azure DevOps, you will need an agent.   To run a pipeline, your SAS code, and the pipeline definition (YAML) must be stored in the Git repository. On agents, the code from the Git repository is cloned and executed.   The agent interacts with a SAS Viya environment, through: SAS Viya Command Line Interface CLI SAS Viya REST APIs, which can be called with curl, Python, SAS or from any programming language. What are Agents? Azure DevOps Agents: Run the Azure Pipelines instructions. Can be Microsoft-hosted or self-hosted. Are hosted on Virtual Machines (VM), Docker containers or Virtual Machine Scale Sets. Can run on Linux, Windows, macOS VMs or in a Docker container. Initiate the communication with the Azure DevOps platform.   Docker Agents Advantages A Docker agent is really a Docker container that runs somewhere, for example on an Azure VM.   Inspired by Jan Kostrubiec's work with Docker agents for GitLab, I have been experimenting lately with Docker agents for Azure DevOps. I discovered a few advantages while using Docker containers: Small footprint. Fast to spin. You can have many Docker container agents running on the same Virtual Machine. That makes better use of the compute resources. You still must pay for parallel jobs running on self-hosted agents if your Git repository is private. Decoupling: if you bring your VM agent down, or you update it, you lose the execution agent. You can spin, update, test and destroy containers, while some are still active, listening for jobs. Create a Docker Agent in Ten Steps The example uses an Azure Linux VM to build your container image.   If you deployed SAS Viya on Azure, the jump-box-vm comes with the SAS Viya deployment. I will be using this machine which is a Linux Ubuntu 20.04.   If you deployed SAS Viya on-premises, pick a Linux machine with inbound and outbound internet connection.   Checklist: Establish communication between the VM and the SAS Viya Azure Kubernetes Service (AKS) cluster. Add an AKS Network Security Group (NSG) inbound rule for the jump-box-vm IP and the port 443. Get an Azure DevOps Personal Access Token (PAT). Set environment variables. Copy SAS Viya certificates (.pem). Download the sas-viya cli. Create a start agent script. Create a Dockerfile. Build the Docker container image. Run the Docker container. The agent is ready and listening for jobs.   Establish Communication The VM where you will run your container must be able to reach the Load Balancer IP of your SAS Viya deployment. Port 443 must be allowed. Create an Azure DevOps Personal Access Token (PAT) You will need to obtain a PAT, for the communication with the Azure DevOps organization. Set Environment Variables The scripts will require several environment variables: AZP_TOKEN: your Azure DevOps personal access token. MYUSER: optional prefix. AZP_AGENT_NAME: your Docker agent name. AZP_POOL: the agent pool where your agent will be registered. AZP_URL: your Azure DevOps Organisation URL. AZP_WORK: a work folder required for the agent. SUDO_USER: power user required to run the scripts. export AZP_TOKEN=Replace_with_the_PAT_you_obtained_previously export MYUSER=Replace_with_SAS_USER_ID # optional prefix # after replacement export AZP_AGENT_NAME=${MYUSER}-dockeragent export AZP_URL=https://dev.azure.com/My_DevOps_Org export AZP_POOL=$MYUSER-sas-viya-jump-vm export AZP_WORK=_work export SUDO_USER=jumpuser   Required Files To build the Docker image you will require a few files: Certificates: PEM file from your SAS Viya deployment. SAS Viya CLI TAR archive. Start script. Dockerfile. Create a folder:   mkdir -p ~/dockeragent && cd ~/dockeragent && ls Copy SAS Viya Certificates Upload the .pem file in the above folder. You can obtain the file by running this kubectl command:   export current_namespace=your_SAS_Viya_Namespace kubectl cp $(kubectl get pod -l app=sas-logon-app -o=jsonpath='{.items[0].metadata.name}'):security/trustedcerts.pem /home/jumpuser/.certs/${current_namespace}_trustedcerts.pem Download the SAS Viya CLI Download the .tgz file, the SAS Viya CLI from the SAS Downloads Page in the same folder. You can log in with your SAS profile.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Create a Start Agent Script Microsoft proposes a start script in Run a self-hosted agent in Docker. The script performs several actions: Uses the environment variables. Downloads packages and agent scripts. Creates an agent. Configures the agent. Starts the agent. Controls the stop of the agent. Create a new file. You might need to change the end of line conversion (Notepad++ example: Edit > EOL Conversion > Unix). Copy the contents:   #!/bin/bash # start.sh set -e if [ -z "$AZP_URL" ]; then echo 1>&2 "error: missing AZP_URL environment variable" exit 1 fi if [ -z "$AZP_TOKEN_FILE" ]; then if [ -z "$AZP_TOKEN" ]; then echo 1>&2 "error: missing AZP_TOKEN environment variable" exit 1 fi AZP_TOKEN_FILE=/azp/.token echo -n $AZP_TOKEN > "$AZP_TOKEN_FILE" fi unset AZP_TOKEN if [ -n "$AZP_WORK" ]; then mkdir -p "$AZP_WORK" fi export AGENT_ALLOW_RUNASROOT="1" cleanup() { if [ -e config.sh ]; then print_header "Cleanup. Removing Azure Pipelines agent..." # If the agent has some running jobs, the configuration removal process will fail. # So, give it some time to finish the job. while true; do ./config.sh remove --unattended --auth PAT --token $(cat "$AZP_TOKEN_FILE") && break echo "Retrying in 30 seconds..." sleep 30 done fi } print_header() { lightcyan='\033[1;36m' nocolor='\033[0m' echo -e "${lightcyan}$1${nocolor}" } # Let the agent ignore the token env variables export VSO_AGENT_IGNORE=AZP_TOKEN,AZP_TOKEN_FILE print_header "1. Determining matching Azure Pipelines agent..." AZP_AGENT_PACKAGES=$(curl -LsS \ -u user:$(cat "$AZP_TOKEN_FILE") \ -H 'Accept:application/json;' \ "$AZP_URL/_apis/distributedtask/packages/agent?platform=$TARGETARCH&top=1") AZP_AGENT_PACKAGE_LATEST_URL=$(echo "$AZP_AGENT_PACKAGES" | jq -r '.value[0].downloadUrl') if [ -z "$AZP_AGENT_PACKAGE_LATEST_URL" -o "$AZP_AGENT_PACKAGE_LATEST_URL" == "null" ]; then echo 1>&2 "error: could not determine a matching Azure Pipelines agent" echo 1>&2 "check that account '$AZP_URL' is correct and the token is valid for that account" exit 1 fi print_header "2. Downloading and extracting Azure Pipelines agent..." curl -LsS $AZP_AGENT_PACKAGE_LATEST_URL | tar -xz & wait $! source ./env.sh trap 'cleanup; exit 0' EXIT trap 'cleanup; exit 130' INT trap 'cleanup; exit 143' TERM print_header "3. Configuring Azure Pipelines agent..." ./config.sh --unattended \ --agent "${AZP_AGENT_NAME:-$(hostname)}" \ --url "$AZP_URL" \ --auth PAT \ --token $(cat "$AZP_TOKEN_FILE") \ --pool "${AZP_POOL:-Default}" \ --work "${AZP_WORK:-_work}" \ --replace \ --acceptTeeEula & wait $! print_header "4. Running Azure Pipelines agent..." trap 'cleanup; exit 0' EXIT trap 'cleanup; exit 130' INT trap 'cleanup; exit 143' TERM chmod +x ./run.sh # To be aware of TERM and INT signals call run.sh # Running it with the --once flag at the end will shut down the agent after the build is executed ./run.sh "$@" & wait $!   Save it as start.sh in the dockeragent folder. Create a Dockerfile The Dockerfile creates a Docker container (agent) image for a SAS Viya deployment: Pulls an image. Install packages. Copies the .pem file in a folder. Copies the .tgz file in a folder and extracts from it. Installs the sas-viya cli. Runs the start.sh script to configure the Azure DevOps agent.   # SAS Viya Docker Agent for Azure DevOps # v 0.2 # Starting Point: https://learn.microsoft.com/en-us/azure/devops/pipelines/agents/docker?view=azure-devops#use-azure-kubernetes-service-cluster # SAS Viya specific elements # Tested with SAS Viya Stable and LTS 2023.03 # Added by Bogdan Teleuca 2023 June 23 # Contribution by Jan Kostrubiec FROM ubuntu:20.04 RUN DEBIAN_FRONTEND=noninteractive apt-get update RUN DEBIAN_FRONTEND=noninteractive apt-get upgrade -y RUN DEBIAN_FRONTEND=noninteractive apt-get install -y -qq --no-install-recommends \ apt-transport-https \ apt-utils \ ca-certificates \ curl \ git \ iputils-ping \ jq \ lsb-release \ software-properties-common \ python3-pip # SAS Viya specific elements \ jq # SAS Viya specific elements RUN curl -sL https://aka.ms/InstallAzureCLIDeb | bash # SAS Viya specific elements # Copy the SAS Viya TLS certificate WORKDIR /home/jumpuser/.certs COPY ./gelenv_trustedcerts.pem . # Copy and unpack the sas-viya cli tgz file - Release 2023.06 # Source: SAS Downloads Page https://support.sas.com/downloads/package.htm?pid=2512 WORKDIR /opt/sas/viya/home/bin ADD ./sas-viya-cli-1.21.10-linux-amd64.tgz . # Make the SAS-Viya CLI file executable and install all the plugins RUN chmod +x /opt/sas/viya/home/bin/sas-viya && /opt/sas/viya/home/bin/sas-viya plugins install --repo SAS all --force # # end specifically for a SAS Viya Agent # Can be 'linux-x64', 'linux-arm64', 'linux-arm', 'rhel.6-x64'. ENV TARGETARCH=linux-x64 WORKDIR /azp COPY ./start.sh . RUN chmod +x start.sh ENTRYPOINT [ "./start.sh" ] Build the Docker Container Image Before running the script, the environment variables must be correctly set.   # Check vars echo $AZP_AGENT_NAME echo $AZP_URL echo $AZP_POOL echo $AZP_WORK echo $SUDO_USER # Build the image docker build -t dockeragent:latest . # List the image docker image ls The build can take 3 to 5 minutes the first time, or even more.   When the build is complete, your Docker image is ready.     Run the Docker Container Finally, the docker command gets the latest version of the agent, configures it, and runs the agent. It registers the agent in the specified agent pool, in your Azure DevOps Organisation.   docker run -e AZP_URL=${AZP_URL} -e AZP_TOKEN=${AZP_TOKEN} -e AZP_AGENT_NAME=${AZP_AGENT_NAME} -e AZP_POOL=${AZP_POOL} dockeragent:latest       The Agent is Ready and Listening for Jobs     Discussion Thanks to my colleague Jan Kostrubiec for inspiring me to try Docker agents and helping me build a decent Dockerfile for SAS Viya. Conclusion SAS Viya and Azure DevOps can be easily integrated to realise several interesting use cases for our customers: code versioning, running SAS programs, jobs, SAS Studio flows, calling SAS Viya REST APIs, promoting code and content.   The self-hosted agent is key to using Azure DevOps with SAS Viya.   A Docker container can be a self-hosted agent. The containerised agent has several advantages compared to a Virtual Machine: fast to create and to destroy, small footprint, better use of compute resources, therefore optimizing costs and availability. Additional Resources Microsoft-hosted agents vs. self-hosted agents. Microsoft's Run a self-hosted agent in Docker. Virtual Machine Agents: Create a virtual machine on Azure. Read How to Create a SAS Viya Azure Pipelines Agent. Configure communication with the SAS Viya Azure Kubernetes Service cluster. Read How to Make Your Virtual Machine Talk with SAS Viya on Azure. SAS Viya and Azure DevOps Use Cases: A SAS Viya Model Release Pipeline with Azure DevOps A SAS Viya with Data Pipeline with Azure DevOps. A SAS Viya Decision Pipeline with Azure DevOps. SAS Viya CI/CD Pipelines with Azure DevOps.     Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about post content. If you wish to get more information, please write me an email.     Find more articles from SAS Global Enablement and Learning here. ... View more

Ever dreamt of driving to work on your private express lane, instead of using a jammed, potentially dangerous highway? Your dream is real, in the cloud.   Azure ExpressRoute allows you to physically connect on-premises networks into Azure, over private connections, completely bypassing the public Internet. ExpressRoute connections are more reliable, faster, with consistent lower latencies, and have higher security than typical connections over the Internet.   Read this post to learn how you can connect a SAS Viya on Azure deployment to an "on-premises" network, from a different geopolitical region.   Previously   In Part 1, we introduced Azure ExpressRoute, the connectivity models, including ExpressRoute Direct and a few scenarios customers might consider for ExpressRoute Direct.   In Part 2, we created an Azure ExpressRoute Direct, an ExpressRoute Circuit, configured Azure Private Peering, added to the circuit two networks from the same geopolitical region (US) and tested the connection from SAS Viya.   In Part 3, we will connect the data centre from Australia to the same ExpressRoute Direct Circuit.   In a graphical way, the question we want to answer is:   How do we access, privately, over Azure and a connectivity provider's backbone, from SAS Viya...   Select any image to see a larger version. Mobile users: If you do not see this image, scroll to the bottom of the page and select the "Full" version of this post.   data stored in a data centre down-there?     Topology   As a reminder, we proposed a topology and two scenarios to access from SAS Viya data from simulated "on-premises" databases. SAS Viya is deployed in Azure in the East US region. The databases sit in two different "on-premises" data centres, one in US, the other in Australia.     Connect the Australian Network to the ExpressRoute Circuit   At this point, the simulated “on-premises” West US data centre is connected to the ExpressRoute circuit.   If you would try to connect the AP-AUS-Network based in Australia, in the exact same way, you would get an error message:     The error message would say: "The creation of the virtual network gateway connection failed because your circuit in Equinix-Ashburn-DC6 cannot be connected to Australia Southeast on a standard circuit. Virtual network gateway connections on a standard ExpressRoute circuit are only allowed within the same geopolitical region. Please upgrade to a premium SKU."     At this point, first, delete the failed connection.   Second, weigh your options. According to ExpressRoute FAQs, you can update your ExpressRoute circuit to Premium. A diagram in the FAQ shows how the SKUs are designed to allow connectivity across regions.     Avoid Bill Shock   Now it is a good moment to tell you what the cost of this setup is. According to the Azure Pricing Calculator :   ExpressRoute Direct with a minimum port pair speed of 10 Gbps + Premium ExpressRoute circuit of 1 Gbps + 10 TB of data transfer   = USD 6956 / month   You see, your private 10 Gbps lane does not come cheap.   You must know the volumes and the speeds you need before "paying the toll on the ExpressRoute". And the use case must justify the cost.   Alternatives:   Unless you absolutely need a 10 Gbps port pair speed, to fill with 10 circuits of 1 Gbps or one circuit of 10 Gbps, you don't really need ExpressRoute Direct. The classic ExpressRoute (not the ExpressRoute Direct) from 50MBps will cost you from USD 130 / month. The connectivity provider costs as well as the data transfer will add to this bill. There are options, it is up to you to decide how fast you want to transfer data.   Private Does Not Mean Encrypted   Another item I wanted to highlight: by default traffic over an ExpressRoute connection is not encrypted. Therefore, private does not mean encrypted. See ExpressRoute encryption for more details. There are other options: use a Azure Virtual WAN or Configure a Site-to-Site VPN connection over ExpressRoute private peering.   Upgrade the Existing ExpressCircuit to Premium   Coming back to our scenario. You decided to upgrade from SKU Standard to Premium.     While this is a quick win, this might be very costly. Some connectivity providers, such as NextDC or MegaPort claim they can save you costs by allowing you to connect two Local or Standard circuits, through their infrastructure, at a fraction of a cost for Premium.   The connectivity bill optimization is out of the scope of this post series. But know there are alternatives you can research.   Connect the Australian Network to the ExpressRoute Circuit   Connect the simulated “on-premises” Australia Southeast data centre’s vnet, to the same ExpressRoute circuit.     We chose the connection to sit in Australia Southeast. I don’t think it matters where the connection sits. The connection is using the SASViyaHQNetworkERCircuit, provisioned through Equinix-Ashburn-DC6. Therefore, the connection is initiated from the US to Australia, through the Azure and provider's private network.   In Settings, you will have to choose the VNG of type ExpressRoute you created in the Australian network:     Connection Success Looks Like This   In the ExpressRoute circuit’s connections, you will see three connections, just like in the topology diagram, at the beginning of the post.     The AP-AUS-Network Virtual Network Gateway (VNG), VNG-AP-AUS, will also show a connected status.   As you can see, the same VNG can be connected to multiple ExpressRoute circuits, in the same or in different geopolitical regions.     Test SAS Viya Connection to the PostgreSQL Server   To test the connection from SAS Viya, deployed in East US to the "on-premises" PostgreSQL server in Australia Southeast, retrieve the server's private IP, for example 11.0.1.4. Using the example provided in How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 3, in SAS Studio, write the following SAS code. The PostgreSQL Server is represented by the private IP address of the server in AP-AUS-Network (private endpoint). Connect to the default postgres database:   libname GELDBHQ clear; libname GELDBHQ postgres server='11.0.1.4' port=5432 user='viyadep@ausnetworksrv' password='fill_in_here' database=postgres SSLMODE='prefer';     Connection is successful, and you can access the database inside the PostgreSQL Server, deployed "on-premises", in Australia South East:   Conclusions   SAS Viya deployed in Azure East US can now connect to a database, hosted "on-premises" in Australia Southeast, through the ExpressRoute Direct Circuit.   In this post we:   Upgraded an existing ExpressRoute Circuit SKU to Premium to allow us to connect across geopolitical regions. Added a new network to the circuit from the simulated "on-premises" data centre. Tested the connection from SAS Viya to a database to the overseas data centre.   Useful Resources   How to Connect SAS Viya in Azure to On-Prem with ExpressRoute - Part 1. How to Connect SAS Viya in Azure to On-Prem with ExpressRoute - Part 2. Connecting Viya in Azure to On-Prem with Azure VPN, ExpressRoute (Intro). Azure geopolitical regions. Microsoft Datacenters Globe. Azure ExpressRoute Connectivity Models. About ExpressRoute Direct. ExpressRoute FAQs. Tutorial: Create ExpressRoute Direct using the Azure portal. Tutorial: Create and modify peering for an ExpressRoute circuit using the Azure portal. Tutorial: Configure a virtual network gateway for ExpressRoute using the Azure portal.   Related Post Series   How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 1 How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 2 How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 3   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about access to on-premises datacentres using VPN gateways.  If you wish to get more information, please write me an email.   Find more articles from SAS Global Enablement and Learning here. ... View more

Ever dreamt of driving to work on your private express lane, instead of using a jammed, potentially dangerous highway? Your dream is real, in the cloud.   Azure ExpressRoute allows you to physically connect on-premises networks into Azure, over private connections, completely bypassing the public Internet. ExpressRoute connections are more reliable, faster, with consistent lower latencies, and have higher security than typical connections over the Internet.   Read this post to learn how you can connect a SAS Viya on Azure deployment to an "on-premises" network, from the same geopolitical region.   Previously   In Part 1, we introduced Azure ExpressRoute, the connectivity models, including ExpressRoute Direct and a few scenarios customers might consider for ExpressRoute Direct.   Architecture Diagram   We proposed a simple architecture diagram and two scenarios to access from SAS Viya data from "on-premises" databases. SAS Viya is deployed in Azure in the East US region. The databases sit in "on-premises" data centres in the same geopolitical region (US) and in a different one (Australia).   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 Part 2, we will create ExpressRoute Direct, ExpressRoute Circuit, configure Azure Private Peering, add two networks to the circuit and test the connection.   Create ExpressRoute Direct Enable ExpressRoute Direct Feature   In your Azure subscription, enable the feature if not already active.   Create ExpressRoute Direct   Create the ExpressRoute Direct resource. For convenience, I chose to create it in the same East US resource group as SAS Viya resources.   Next, choose your peering location.     Peering Location: The location where you'll connect to the ExpressRoute Direct resource. For more information about peering locations, review ExpressRoute peering locations and connectivity partners.     According to Eigthy20 Solutions: …the ExpressRoute circuits peering / meet-me-location is chosen based on the physical location of customer’s on-prem data centers.   I chose a provider from Ashburn, Virginia (VA), a location not far from the Azure East US region data centers, supposedly located in Richmond, Virginia, according to DgtlInfra. I am therefore "meeting" Microsoft in Ashburn, VA.   At this point, I am hesitating between East US, next to the SAS Viya Azure region and West US, next to the on-premises data centre location. I want to add to the ExpressRoute circuit an Azure virtual network in East US, a network from West US and eventually a third network from Australia Southeast.   The same Eigthy20 Solutions suggest: The “Peering / Meet-me Location” indicates the physical location where customer / connectivity provider peer with Microsoft. This is not linked to “Location” property of the ExpressRoute object created in [the] Azure portal, which refers to the geography where the Azure Network Resource Provider is located. While they are not related, it is a good practice to choose a Network Resource Provider geographically close to the Peering Location of the circuit. In the next few steps, I will create the ExpressRoute Circuit in East US, although it makes more sense to choose a peering location close to your on-premises data centre. But if you want to add to the same circuit, two data centres, geographically remote, where it is best to place your peering location?   I did not reach a firm conclusion, even after reading through Optimize ExpressRoute Routing. At this point I wonder if it is best to create two separate circuits in two different locations, then connect each network to the closest circuit.   What the Experts are Saying   If you are ready for an in-depth session, watch Stephan's Azure ExpressRoute demystified (part 2) whiteboarding session. Towards the middle of his video, he explains beautifully ExpressRoute, Direct and Global Reach. Be ready for keywords such as VLAN, BGP, ASN, VLAN IDs, tagging, QinQ and Dot1Q.   Generate the Letter of Authorization (LOA)   Support requires documentation, such as a Letter of Authorization, that proves you are allowed to use the resources. You can obtain a LOA after you created your ExpressRoute Direct resource.     A Letter of Authorization (LOA) looks like this:   Change the Admin State of Each Link   You can view the Admin state for each link of the ExpressRoute Direct port pair. The Admin state represents if the physical port is on or off. This state is required to pass traffic across the ExpressRoute Direct connection.   Enable cross-connect links 1 and 2.   Important: Billing will begin when admin state is enabled on either link.     Create the ExpressRoute Circuit The ExpressRoute Circuit is a logical connection between your on-premises infrastructure and the Azure cloud.   The choice was to create it in the same resource group where SAS Viya resources are deployed, in East US and close to the peering location.   Another option might be to create the ExpressRoute Direct and Circuit closer to your on-premises data centre in West US.       Select the ExpressRoute Direct resource. Circuit bandwidth is here 1 GB/s. SKU: Standard, for now, as your objective is to link with virtual networks in the same geopolitical region.   After you've created the ExpressRoute circuit, you can link virtual networks to your ExpressRoute circuit.   With this design, to each ExpressRoute Direct resource you can attach 10 Standard circuits. To each circuit you can link 10 VNETs.   ExpressRoute peering   According to ExpressRoute circuits and peering : An ExpressRoute circuit has multiple routing domains/peerings associated with it: Azure public, Azure private, and Microsoft. Each peering is configured identically on a pair of routers (in active-active or load sharing configuration) for high availability. Azure services are categorized as Azure public and Azure private to represent the IP addressing schemes.   Azure private peering   Azure compute services, namely virtual machines (IaaS) and cloud services (PaaS), that are deployed within a virtual network can be connected through the private peering domain. The private peering domain is a trusted extension of your core network into Microsoft Azure. You can set up bi-directional connectivity between your core network and Azure virtual networks (VNets). This peering lets you connect to virtual machines and cloud services directly on their private IP addresses.   You can connect more than one virtual network to the private peering domain.   Configure Azure Private Peering   Important: The subnets IP address ranges must not overlap with the address ranges of the networks you want to add to the circuit.     For example, if SAS Viya’s $PREFIX-vnet address space is 192.168.0.0/16, you can have: ASN: 65020 Ipv4 primary subnet: 192.169.21.16/30 Ipv4 secondary subnet: 192.169.21.20/30 You cannot have: ASN: 65020 Ipv4 primary subnet: 192.168.21.16/30 Ipv4 secondary subnet: 192.168.21.20/30 Because 192.168.21.16/30 overlaps with 192.168.0.0/16.   After the configuration, you shall see:     Virtual Network Gateways (VNG)   Before moving further, you should have created VNGs, of type ExpressRoute, for each network you want to add to the circuit. For more details see Part 1 or Tutorial: Configure a virtual network gateway for ExpressRoute using the Azure portal.   Connect the SAS Viya VNet to an ExpressRoute Circuit   In this step we are going to connect SAS Viya’s $PREFIX-vnet to the ExpressRoute circuit created.     In Settings, you will have to choose the VNG of type ExpressRoute from $PREFIX-vnet:     Connection Success Looks Like This   In the ExpressRoute circuit’s connections, you shall see:     Because the connection is bi-directional, the $PREFIX-vnet's VNG will also show a connected state.   Note: If the connection fails with a message “BGP with Peering Key…”, you need to activate your Azure Peering or check there is no address space overlap between the peering subnets and your network.   Connect the HQ-Network VNet to an ExpressRoute Circuit Connect the simulated “on-premises” West US data centre’s vnet, to the same ExpressRoute circuit.       Now, two networks are connected to the same ExpressRoute Circuit.     Test SAS Viya Connection to the PostgreSQL Server   Using the example provided in How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 3, in SAS Studio, write the following SAS code. The PostgreSQL Server is represented by the private IP address of the server in HQ-Network (private endpoint). Connect to the default postgres database:   libname GELDBHQ clear; libname GELDBHQ postgres server='172.16.0.4' port=5432 user='viyadep@hqnetworksrv' password='fill_in_here' database=postgres SSLMODE='prefer';   Connection is successful, and you can access the database inside the PostgreSQL Server, deployed "on-premises", in West US:         Conclusions   SAS Viya deployed in Azure East US can connect to a database, hosted "on-premises" in West US, through the ExpressRoute Direct Circuit.   In this post we: Created an ExpressRoute Direct resource. Created an ExpressRoute Circuit. Configured Azure Private Peering in the circuit Added two networks to the circuit, one from Azure, the other from the simulated "on-premises" data centre. Tested the connection from SAS Viya to a database.   In Part 3   Read How to Connect SAS Viya in Azure to On-Prem with ExpressRoute - Part 3, where you will learn how to connect to the same ExpressRoute circuit a network from a different geopolitical region.   Useful Resources How to Connect SAS Viya in Azure to On-Prem with ExpressRoute - Part 1. Connecting Viya in Azure to On-Prem with Azure VPN, ExpressRoute (Intro). Azure geopolitical regions. Microsoft Datacenters Globe. Azure ExpressRoute Connectivity Models. About ExpressRoute Direct. ExpressRoute FAQs. Tutorial: Create ExpressRoute Direct using the Azure portal. Tutorial: Create and modify peering for an ExpressRoute circuit using the Azure portal. Tutorial: Configure a virtual network gateway for ExpressRoute using the Azure portal. Related Post Series How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 1 How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 2 How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 3   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about access to on-premises datacentres using VPN gateways.  If you wish to get more information, please write me an email.   Find more articles from SAS Global Enablement and Learning here. ... View more

Ever dreamt of driving to work on your own private express lane, instead of using a jammed, potentially dangerous highway? Your dream is real, in the cloud.   Azure ExpressRoute allows to physically connect on-premises networks into Azure, over private connections. ExpressRoute connections completely bypass the public Internet. ExpressRoute connections are more reliable, faster, with consistent lower latencies, and have higher security than typical connections over the Internet.   Read this post to learn how you can connect a SAS Viya on Azure deployment to an on-premises network by using ExpressRoute. When the connection is in place, you can access from SAS Viya data from a database in your on-premises data centre, from the same or different geopolitical region.   Stephen Foerster mentioned in Connecting Viya in Azure to On-Prem with Azure VPN, ExpressRoute (Intro): “Microsoft recommends two different mechanisms for connecting SAS Viya in Azure to on-premises resources […] Both methods require Azure Virtual Network (VNET) gateways to facilitate communication. ExpressRoute offers added security and performance by sending traffic over a dedicated private line.”   Context and Objectives We want to realize two scenarios, with SAS Viya deployed in the Azure East US region. We want to access through Azure ExpressRoute: Data from a database in a data centre deployed in West US. Data from a database in a data centre deployed in Australia Southeast. The first scenario requires connectivity across the same Azure geopolitical region (West US and East US are in North America).   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The second scenario requires connectivity across different geopolitical region (Australia Southeast is in Oceania).     If you haven’t yet explored the Microsoft Datacenters Globe, do it, it’s pretty cool!   Simulated Data Centre In this post we are going to simulate the on-premises data centres as Azure networks. Why we chose to simulate the data centre? There are many types of on-premises VPN devices, connectivity providers and it is not possible to describe their configuration in a simple post series.   What you need to remember is the logic and the configuration steps.   Architecture Diagram By the end of the post series, we want to realize the following architecture diagram:     Where:   Azure side - $PREFIX-vnet, the SAS Viya virtual network deployed in the East US region: $PREFIX-aks-subnet is the sub-network of the SAS Viya Azure Kubernetes Service (AKS) cluster and its resources. $PREFIX-misc-subnet is the sub-network of the several virtual machines, including $PREFIX-jump-VM. A separate gateway subnet is needed for the Azure Virtual Network Gateway (VNG). A VNG with type ExpressRoute. The On-premises side is composed of:   HQ-Network is the on-premises network in a West US data centre. It has an Applications subnet. This subnet hosts a database server and a private endpoint. We created a private endpoint, to force the database traffic, network-to-network. We want to avoid SAS Viya connections directly to the database over the Public Internet. A separate gateway subnet is required for the on-premises VNG. The VNG type is ExpressRoute. AP-AUS-Network is another on-premises network, in a data centre in Melbourne, in Australia Southeast: It has a Databases subnet. This subnet hosts a database server and a private endpoint . A separate gateway subnet required for the on-premises VNG. The VNG type is also ExpressRoute. First, $PREFIX-vnet, where SAS Viya sits, will connect to the ExpressRoute Direct Circuit via the VNG. Then, HQ-Network and finally, AP-AUS-Network.   ExpressRoute Connectivity Models According to Microsoft, ExpressRoute allows you to create a connection between your on-premises network and the Microsoft cloud in four different ways, CloudExchange Co-location, Point-to-point Ethernet Connection, Any-to-any (IPVPN) Connection, and ExpressRoute Direct. Connectivity providers may offer more than one connectivity models. You can work with your connectivity provider to pick the model that works best for you.     ExpressRoute Direct Model Because I am simulating the on-premises data centres and because I am not hosting my data centre with a connectivity provider, I chose the ExpressRoute Direct model.   There are particularly good reasons and potential cost savings to work with a connectivity provider. I encourage you to do your own research.   According to About ExpressRoute Direct: “You can connect directly into the Microsoft global network at a peering location strategically distributed across the world. ExpressRoute Direct provides dual 100-Gbps or 10-Gbps connectivity that supports Active/Active connectivity at scale.”   Compared to VPN Gateways, where the tunnel throughput is typically 100 Mbps, ExpressRoute Direct throughput is just… massive.   100-Gbps or 10-Gbps is just mind-blowing fast. Faster than many wired connections in your local office.   What scenarios should customers consider with ExpressRoute Direct? According to ExpressRoute FAQs: “ExpressRoute Direct provides customers with direct 100 Gbps or 10-Gbps port pairs into the Microsoft global backbone. The scenarios that provide customers with the greatest benefits include: Massive data ingestion, physical isolation for regulated markets [or government], and dedicated capacity for burst scenario, like rendering.”   Azure Side Resources   SAS Viya Virtual Network To connect the SAS Viya virtual network (VNET), $PREFIX-VNET, using ExpressRoute, you need to create a Virtual Network Gateway (VNG) in $PREFIX-VNET. The VNG must be of type ExpressRoute. For more information, read VNGs – ExpressRoute type.   Add a Gateway Subnet The VNG requires a dedicated GatewaySubnet. Therefore, in $PREFIX-vnet create a GatewaySubnet to host the VNG. As $PREFIX-vnet has an address space of 192.168.0.0/16 the gateway subnet has an address space: 192.168.3.0/27.   Create the Virtual Network Gateway VNG Type: ExpressRoute Name: VNG-$PREFIX Select the gateway subnet created.     Simulated On-premises Resources For the sake of simplicity, we are going to assume the on-premises resources, have already been created. You can find a deployment example in How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 3.   You will also need an ExpressRoute VNG in each of the networks.   Conclusions In this post we introduced Azure ExpressRoute, the connectivity models, including ExpressRoute Direct and scenarios customers might consider for ExpressRoute Direct.   We proposed a topology and two scenarios to connect from SAS Viya deployed in Azure in East US to data from databases in "on-premises" data centres in the same or in a different geopolitical region.   In Part 2 Read the next post How to Connect SAS Viya in Azure to On-Prem with ExpressRoute - Part 2, where you will learn how to: Create the ExpressRoute Direct Resource. Discuss briefly peering locations and ExpressRoute peering. Create the ExpressRoute Circuit. Configure Azure Private Peering in an ExpressRoute Circuit. Useful Resources Connecting Viya in Azure to On-Prem with Azure VPN, ExpressRoute (Intro) Azure geopolitical regions. Microsoft Datacenters Globe. Azure ExpressRoute Connectivity Models. About ExpressRoute Direct. ExpressRoute FAQs. Related Post Series How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 1 How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 2 How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 3 Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about access to on-premises datacentres using VPN gateways. If you wish to get more information, please write me an email.   Find more articles from SAS Global Enablement and Learning here. ... View more

@StephenFoerster mentioned in Connecting Viya in Azure to On-Prem with Azure VPN, ExpressRoute (Intro): “Microsoft recommends two different mechanisms for connecting SAS Viya in Azure to on-premises resources […] Both methods require Azure Virtual Network (VNET) gateways to facilitate communication. Site to Site VPN connections also require VPN devices configured on premises.”   Read this post to learn how you can test the connection from SAS Viya on Azure to an on-premises database over site-to-site VPN gateways.   Context and Objective The previous posts, How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 1 and How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 2 explained how to create a site-to-site VPN gateway connection to a simulated on-premises data centre. We simulated the data centre as an Azure network, for pure convenience.   In this post, we will deploy a PostgreSQL server in the "on-premises" network and we will connect to it from SAS Viya. We will also look at the traffic over the Virtual Network Gateways.   Architecture Diagram Looking at the architecture diagram, we want to access the hqnetworksrv PostgreSQL Server from HQ-Network VNET, the Applications subnet: First, from SAS Viya deployed in the $PREFIX-vnet, the $PREFIX-aks-subnet. Second, from a Virtual Machine deployed in the $PREFIX-vnet, the $PREFIX-misc-subnet. The data access will happen over the established VPN tunnel. The VPN tunnel allows for encrypted network traffic over the public Internet, between an Azure virtual network and on-premises data centres.     Assumptions SAS Viya deployed on Azure, in the $PREFIX-vnet mentioned in the diagram. You created the resources mentioned in How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 1 and How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 2.   Optional: Enable Network Watcher - VPN Troubleshoot Network Watcher has a useful tool, called the VPN troubleshoot. You can use it to diagnose VPN connections between networks.   Enable VPN Troubleshoot. You will need a storage account. Later, after you generated some traffic over the VPN tunnel, you can check later the logs in the storage account.     Deploy "On-premises" Resources   Deploy a PostgreSQL Server The simulated data center is an Azure Database for PostgreSQL server in Azure.   On the upper-left side of the screen in the Azure portal, select Create a resource > Databases > Azure Database for PostgreSQL.   In Azure Database for PostgreSQL deployment option, select Single server and provide this information: Subscription – select your subscription. Resource group – HQ-rg. You created the resource group earlier. Server details: Server name: hqnetworksrv . Admin username: Enter viyadep . Password: the password must be at least 8 characters long and meet the defined requirements. Location: East US. Version: Select the required database version of the PostgreSQL server. Compute + Storage: Select the pricing tier needed for the server based on the workload: General Purpose. Select OK. Select Review + create. You're taken to the Review + create page where Azure validates your configuration. When you see the Validation passed message, select Create. The server must be deployed before you proceed any further.       What are Azure Private Endpoints? Let's stop for a minute and introduce a new concept, the Azure Private Endpoint.   Azure Private Endpoints: What Are They and What Are Their Use Cases? explains them as: “a powerful tool that allow you to securely access your Azure services over a private endpoint. With Private Endpoints, you can connect to Azure services without exposing your data to the public internet, improving security and compliance for your applications.   Azure Private Endpoints are commonly used in scenarios where customers want to access Azure services from a private network, such as an on-premises network or a virtual network in Azure. In these scenarios, customers can use Private Endpoints to connect to Azure services over a private connection without having to expose their data to the public internet.”   In Azure they can be used for an entire range of services. Read more about it here.   Deploy a Private Endpoint for your PostgreSQL Server In our case, the purpose of a private endpoint is to allow clients to securely access data over a Private Link.  Clients can be: SAS Viya or a VM deployed in an Azure virtual network (VNet).   The private endpoint uses a separate IP address from the VNet address space for each service, like the PostgreSQL server database.   Network traffic between SAS Viya, and the PostgreSQL server will traverse over the VNet and a private link on the Microsoft backbone network, eliminating exposure from the public internet.   On the upper-left side of the screen in the Azure portal, select Create a resource > Networking > Private Link.   In Private Link Center - Overview, on the option to Build a private connection to a service, select Create a private endpoint.     In Create a private endpoint - Basics, enter or select this information:   Project details: Subscription: Select your subscription. Resource group: Select your "on-premises" resource group, HQ-rg. Instance Details: Name Enter hqnetworkPE. If this name is taken, create a unique name. Region: East US. In Create a private endpoint - Resource, enter or select this information: Connection method: Choose Connect to an Azure resource in my directory. Subscription: Select your subscription. Resource type: Microsoft.DBforPostgreSQL/servers. Resource: hqnetworksrv . This is the PostgreSQL server you deployed earlier. Target sub-resource: postgresqlServer. In Virtual Network enter this information: Virtual network: HQ-Network . Subnet: Applications. Dynamically allocate IP addresses. In DNS: Integrate with private DNS zone, select Yes. Private DNS Zone: choose the default: e.g.  (new)privatelink.postgres.database.azure.com . Note: Use the predefined private DNS zone for your service or provide your preferred DNS zone name.     Retrieve the Private IP Address When the Private Link has been created, Go to Resource and from DNS configuration, get the private IP address generated, for example: 172.16.0.4.     Deny Public Network Access When we access the PostgreSQL server from SAS Viya, we want to stay away from the public internet.   We want to force the database traffic via the private link, over the VNet and on the Microsoft backbone network. To force the traffic, disable the public network access for PostgreSQL Server:     Test the Connection to the “On-premises” Database from SAS Viya In SAS Studio, write the following SAS code. The SAS server becomes the private IP address of the PostgreSQL Server (private endpoint). Connect to the default postgres database:   libname GELDBHQ clear; libname GELDBHQ postgres server='172.16.0.4' port=5432 user='viyadep@hqnetworksrv' password='fill_in_here' database=postgres SSLMODE='prefer';   Connection is successful, and you can access the database:       Virtual Network Gateway Traffic If you are going to your VNG-HQ-Network page you can already see traffic over the tunnel ingress and egress.     The traffic in the VPN tunnel was generated by the traffic between SAS Viya and the PostgreSQL server database.   Egress in the world of networking implies traffic that exits an entity or a network boundary, while ingress is traffic that enters the boundary of a network.   Network Watcher VPN Troubleshoot: Proof Packets are Sent over the VPN Tunnel The Network Watcher VPN Troubleshoot will send data to a storage account of your choice. Check the storage account after you access the database from SAS Studio.   Connectivity State : Connected Remote Tunnel Endpoint : 74.235.4.188 Ingress Bytes (since last connected) : 128412 B Egress Bytes (since last connected) : 31889 B Ingress Packets (since last connected) : 383 Packets Egress Packets (since last connected) : 360 Packets Ingress Packets Dropped due to Traffic Selector Mismatch (since last connected) : 0 Packets Egress Packets Dropped due to Traffic Selector Mismatch (since last connected) : 0 Packets Bandwidth : 0 b/s Peak Bandwidth : 0 b/s Connected Since : 5/2/2023 8:33:49 AM PeakPackets : 0 TotalFlowCount : 0 Throttle : False   The relevant part is in the first six lines, the ingress or egress bytes and packets, proving data travels through the VPN tunnel.   Conclusions You can access the data from SAS Viya over a VPN tunnel. The traffic travels VNET-to-VNET (Virtual Network Gateway to Local Network Gateway) in an encrypted fashion.   There are advantages of using Azure gateways and site-to-site connections. You do not need to whitelist the SAS Viya Load Balancer Public IP on the PostgreSQL server side. Nor do you require an inbound rule in the SAS Viya AKS Network Security Group for the PostgreSQL Server IP or the port 5432 (PostgreSQL).   In addition, we deployed a private endpoint and denied public access to "force" the PostgreSQL server traffic to stay inside the network and only travel through the VPN tunnel, the site-to-site connection.   SAS customers can use private endpoints for their Azure services and access data from SAS Viya. The traffic will flow over the Microsoft backbone. Private endpoints are even more relevant when you peer the SAS Viya network with other networks in Azure. ExpressRoute Read the next post, How to Connect SAS Viya in Azure to On-Prem with ExpressRoute – Part 1, where you will learn how to take connectivity to a next level. Useful Resources How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 1 How to Connect SAS Viya in Azure to On-Prem with VPN Gateways – Part 2 Connecting Viya in Azure to On-Prem with Azure VPN, ExpressRoute (Intro) SAS Cloud Data Exchange for the SAS Viya Platform Connect on-premises networks to Azure by using site-to-site VPN gateways Exercise - Prepare Azure and on-premises virtual networks by using Azure CLI commands Exercise - Create a site-to-site VPN gateway by using Azure CLI commands CIDR to IP range   Thank you for your time reading this post. If you liked the post, give it a thumbs up! Please comment and tell us what you think about access to on-premises datacentres using VPN gateways. If you wish to get more information, please write me an email. ... View more