Overview

ScaleArc is a complete SQL proxy and can direct SQL queries and connections across several servers based on user-defined regular expression (Regex) rules. The Query Routing Policies and rules can be based on client IP address, database user name, database name, or any part of the SQL query or SQL comment and can be exported as a JSON array if necessary.

This article describes the procedure to export ScaleArc Query Routing Policies using the ScaleArc REST API.

Information

Follow the steps outlined below to export the currently defined Query Routing Policies as well as the rules/patterns defined under those policies:

1. Review the currently defined Query Routing Policies by navigating to CLUSTERS > Settings column > Load Balancing and Routing > Query Routing Policies tab. 
   
2. Obtain the API key by navigating to SETTINGS > API and click on the Get Apikey button.
    
   
   
3. Authenticate with ScaleArc admin credentials when prompted to fetch the API key needed to fire API requests. 
   
4. From the drop-down menu find "cluster/-ID-/sharding/rules" and click GET 
   
5. Replace -ID- in the URL with the actual number of your cluster, found in the CLUSTERS control panel, right under the cluster name.
   
6. Fire the CURL command by clicking on the EXECUTE button or copying the URL to your preferred CLI e.g PowerShell 
   

   curl -k -X GET https://<scalearc_ip>/api/cluster/1/sharding/rules?apikey=57f6b374004e368af4dc27dff56ecf4d44bdb62c

7. Export the Query Routing Policies with the following API calls:
   
   • For query routing rules: curl -k -X GET https://<scalearc_ip>/api/cluster/<ID>/sharding/rules?apikey=<apikey_value>
   Example result:
   

   {
     "success": true,
     "message": "Information for query sharding rule(s).",
     "timestamp": 1599008857,
     "surl": "",
     "data": {
       "total": 1,
       "sharding_rules": [
         {
           "id": 1,
           "database_name": "All Databases",
           "user_name": "All Users",
           "source_ip": "All IPs",
           "rule_count": 4,
           "order": 1,
           "status": "on"
         }
       ]
     }
   }

   
   
8. To obtain the list of patterns in a query routing policy: curl -k -X GET https://<scalearc_ip>/api/cluster/25/sharding/rule/<number>/patterns?apikey=<apikey_value>
   
   

   {
     "success": true,
     "message": "Information for query routing pattern.",
     "timestamp": 1599008037,
     "surl": "",
     "data": {
       "sharding_patterns": [
         {
           "patternid": 1,
           "pattern": "RULE1",
           "route_to": "Read/Write",
           "failsafe": "on",
           "and_or": "and",
           "order": 1,
           "status": "on"
         },
         {
           "patternid": 2,
           "pattern": "RULE2",
           "route_to": "Read Only",
           "failsafe": "on",
           "and_or": "and",
           "order": 2,
           "status": "on"
         },
         {
           "patternid": 3,
           "pattern": "RULE3",
           "route_to": "192.168.0.50",
           "failsafe": "on",
           "and_or": "and",
           "order": 3,
           "status": "on"
         },
         {
           "patternid": 4,
           "pattern": "RULE4",
           "route_to": "Read/Write",
           "failsafe": "on",
           "and_or": "and",
           "order": 4,
           "status": "on"
         }
       ],
       "total": 4
     }
   }