PostgreSQL Common Table Expressions (CTEs): A Powerful Tool for Complex Queries

Introduction

Common Table Expressions (CTEs) are a powerful feature in PostgreSQL that allow you to define temporary named subqueries within a larger query. CTEs can be used to simplify complex queries, make them more readable and reusable, and improve performance.

Benefits of using CTEs

There are many benefits to using CTEs in PostgreSQL, including:

• Improved readability and maintainability: CTEs can help to break down complex queries into smaller, more manageable parts. This can make queries easier to read and understand, and easier to maintain in the future.
• Reusability: CTEs can be reused within the same query, or in other queries. This can save time and effort when writing complex queries.
• Performance: CTEs can be optimized by the PostgreSQL query optimizer, which can improve the performance of complex queries.

Syntax

The syntax for CTEs in PostgreSQL is as follows:

SQL

WITH cte_name (column_list) AS (

  cte_query

)

SELECT ...

FROM cte_name

The WITH clause is used to introduce the CTE. The cte_name is the name of the CTE, and the column_list is the list of columns that the CTE will return. The cte_query is the subquery that defines the CTE. The SELECT statement in the main query then refers to the CTE by name.

Examples

Here are some examples of how to use CTEs in PostgreSQL:

Calculate running totals:

SQL

WITH running_totals AS (

  SELECT

    customer_id,

    SUM(order_amount) AS running_total

  FROM orders

  GROUP BY customer_id

  ORDER BY order_date

)

SELECT

  customer_id,

  running_total

FROM running_totals

This CTE calculates the running total of order amounts for each customer. The main query then selects the customer ID and running total from the CTE.

Rank rows:

SQL

WITH ranked_rows AS (

  SELECT

    customer_id,

    RANK() OVER (PARTITION BY product_id ORDER BY order_amount DESC) AS rank

  FROM orders

)

SELECT

  customer_id,

  rank

FROM ranked_rows

This CTE ranks the customers for each product by order amount, from highest to lowest. The main query then selects the customer ID and rank from the CTE.

Filter data:

SQL

WITH filtered_data AS (

  SELECT

    *

  FROM orders

  WHERE order_date >= CURRENT_DATE - INTERVAL '1 MONTH'

)

SELECT

  *

FROM filtered_data

This CTE filters the orders table to only include orders that were placed in the past month. The main query then selects all rows from the filtered table.

Recursive CTEs

Recursive CTEs allow you to write queries that can traverse hierarchical data structures, such as a tree or graph. For example, the following CTE can be used to recursively calculate the total number of descendants of each node in a tree:

SQL

WITH recursive descendants AS (

  SELECT

    node_id,

    COUNT(*) AS descendant_count

  FROM tree

  GROUP BY node_id

  UNION ALL

  SELECT

    tree.parent_id,

    descendant_count + 1

  FROM tree

  JOIN descendants ON tree.node_id = descendants.descendant_id

)

SELECT

  node_id,

  descendant_count

FROM descendants

This CTE works by first calculating the number of direct descendants for each node in the tree. Then, it recursively adds the number of descendants of each descendant to the total number of descendants for the parent node.

Conclusion

CTEs are a powerful tool that can be used to simplify complex queries, make them more readable and reusable, and improve performance. If you are writing complex SQL queries in PostgreSQL, I encourage you to learn more about CTEs. They can be a valuable addition to your SQL toolbox.

#Database #SQL #DataAnalysis #CTEs #CareerGrowth #postgres

Window functions in PostgreSQL: The secret weapon of SQL ninjas

Window functions are one of the most powerful features in PostgreSQL, but they're also one of the least understood. In this blog post, we're going to demystify window functions and show you how to use them to solve real-world problems.

What are window functions?

Window functions allow you to perform calculations on a subset of rows, or "window", of data within a table. This can be useful for a variety of tasks, such as calculating running totals, ranking rows, and finding outliers.

Why use window functions?

Window functions offer a number of advantages over traditional SQL aggregation functions. First, window functions can be used to perform calculations on a subset of rows, rather than the entire table. This can be useful for tasks such as calculating running totals or ranking rows within a specific group.

Second, window functions can be used to perform calculations that involve multiple columns. For example, you could use a window function to calculate the average order amount for each customer over the past 30 days. This type of calculation would be difficult to perform using traditional SQL aggregation functions.

How do window functions work?

Window functions work by defining a "window" of rows on which to perform a calculation. The window can be defined by using a PARTITION BY clause and an ORDER BY clause.

The PARTITION BY clause divides the data into groups. The ORDER BY clause sorts the data within each group. Once the window is defined, the window function is applied to each row in the window.

Examples of window functions

Here are a few examples of window functions in PostgreSQL:

• SUM() OVER(): Calculates the sum of the values in the window.
• AVG() OVER(): Calculates the average of the values in the window.
• COUNT() OVER(): Counts the number of rows in the window.
• RANK() OVER(): Ranks the rows in the window from lowest to highest.
• DENSE_RANK() OVER(): Ranks the rows in the window without gaps.
• PERCENT_RANK() OVER(): Calculates the percentile rank of the row in the window.

Using window functions in SQL queries

To use window functions in SQL queries, you use the OVER() clause. The OVER() clause defines the window on which to perform the calculation.

Here is an example of a SQL query that uses a window function:

SELECT

  customer_id,

  order_amount,

  SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total

FROM orders;

This query will calculate the running total of order amounts for each customer. The PARTITION BY clause ensures that the running total is calculated separately for each customer. The ORDER BY clause ensures that the running total is calculated in chronological order.

Advanced window function techniques

Window functions can be used in conjunction with other SQL features, such as subqueries and CTEs, to perform complex calculations and implement complex business logic.

For example, you could use a window function to calculate the average order amount for each customer over the past 30 days. You could also use a window function to identify customers who have placed at least three orders in the past month.

Conclusion

Window functions are a powerful tool that can be used to solve a variety of real-world problems in PostgreSQL. If you're not already familiar with window functions, I encourage you to learn more about them. They can be a valuable addition to your SQL toolbox.

Now go forth and conquer the world with your newfound window function powers!

Most common and useful postgres metacommands

 

Postgres MetaCommands Cheat Sheet

 

Cheat Sheet that serves as a quick reminder of many of the most common and useful metacommands.

 

Meta Command	Description
\l	List Database in current connection to Database
\c <database>	Connect to a database
\dt	List Database Tables
\d <tablename>	Describe Table
\dn	List Schemas
\du	List Users and their Roles
\du <user>	List details about a specific user
\df	List Functions
\dv	List Views
\o <filename>	Save query results to a file
\i <filename>	Execute commands inside a file
\q	Quit psql

 

Managing BigAnimal Clusters with BigAnimal’s APIs in Ansible

In this blog posting we will be looking at BigAnimal cluster management via BigAnimal’s APIs with Ansible.

In case you would like to review details about BigAnimal, you can take a look at a previous blog posting: “Managing BigAnimal Clusters with BigAnimal APIs”, in which we reviewed and explained what is: 

• DBaaS
• BigAnimal
• BigAnimal Cluster

We also discussed What BigAnimal is, its features and capabilities.

Ansible

Ansible is an open-source provisioning, and configuration management software written by Michael DeHaan and acquired by Red Hat in 2015.

Why use Ansible and BigAnimal?

A few reasons for utilizing Ansible together with BigAnimal are:

• Ansible is a tool for automating infrastructure - Infrastructure as Code
• Deploying and managing applications
• Yaml based, simple to learn, and organized within yaml files named “Playbooks”
• Easy to assimilate into DevOps pipelines
• Simplifies the process of provisioning resources in the Cloud
• Easy to learn

Prerequisites

• Ansible previously installed
• Previously configured BigAnimal credentials and token
• Completed ‘data.json’ for passing all the cluster configuration to the API
• Code that has been provided for this blog posting

Obtaining a Token

A BigAnimal token is created by executing the script ‘get-token.sh’ as shown below:

get-token.sh

 

After following the instructions displayed on the console, a successful token assignment looks somewhat as below

{   "access_token": "<yourtoken>",   "refresh_token": "v1.MZY5PHwcVWeDw82c5C2qkRa5U3Cenp3eD2beagy6DmQCxf7FGU9U9XczyDjWaJmboeoDYwbzgyUBt80lNINMOfk",   "scope": "openid profile email offline_access",   "expires_in": 86400,   "token_type": "Bearer" }

 

From the message above, we can tell how long the token will last, which is listed in seconds under the “expires_in” setting.

Should there be a need to obtain more tokens the same process can be executed as many times as needed.

Creating a Cluster

Once you have all the prerequisites readily available, we can proceed to edit the “data.json” file with the desired cluster configuration:

{   "clusterName": "DO-ansible-1",   "instanceType": {     "cpu": 2,     "id": "azure:Standard_E2s_v3",     "instanceType": "E2s v3",     "ram": 16   },   "password": "SuperDuper123",   "pgConfigMap": [     [       "max_connections",       "100"     ]   ],   "postgresType": {     "dockerImage": "",     "id": "epas"   },   "postgresVersion": "14",   "privateNetworking": true,   "provider": {     "cloudProviderId": "azure"   },   "region": {     "regionId": "westus2"   },   "replicas": 3,   "volumeProperties": "P1",   "volumeType": {     "configFieldsList": [       "secretName",       "shareName"     ],     "displayName": "Azure Premium Storage",     "id": "azurepremiumstorage",     "storageClass": "managed-premium"   },   "zoneRedundantHa": true }

 

The values to be configured should be:

• Cluster Name
• Password
• Postgresql Type
• Postgresql Version
• Provider
• Region
• Instance Type
• Volume Type
• Volume Properties
• High Availability
• Networking
• Confirmation of cluster creation

The next step is to edit the: “variables.yml” file and look for the section where you assign the token, which is on line 7. The “variables.yml” file content should look similar to this:

token: "<yourtoken>" biganimal_server: https://portal.biganimal.com/ # Match the name, provider pgType and pgVersion to desired filters list_endpoint: api/v1/clusters?name=DO-ansible&provider=azure&pgType=epas&pgVersion=14&sort=%2Bname provision_endpoint: api/v1/clusters status_endpoint: api/v1/clusters pgId: <your_biganimal_cluster_id> status_healthystate: Cluster in healthy state

 

The values available for configuration within this file are:

• token
• biganimal_server - URL for the BigAnimal portal
• list_endpoint - suffix to be appended to the “biganimal_server” url
• provision_endpoint - suffix to be appended to the “biganimal_server” url
• status_endpoint - suffix to be appended to the “biganimal_server” url
• pgId - BigAnimal cluster Postgres Id
• status_healthystate - Text to display when a BigAnimal Cluster is in healthy state

 

The code to create a cluster is located inside the “provision.yml” file, it looks like this

 

# Original work by: EnterpriseDB # Author: Doug Ortiz # Date: March 03, 2022 # Version: 1.0 # Copyright (c) 2020 EnterpriseDB - hosts: localhost   vars_files:     - ./variables.yml   tasks: #  - name: Print token #    debug: msg="{{ token }}"       - name: Provision  BigAnimal Cluster     uri:       url: "{{ biganimal_server }}{{ provision_endpoint }}"       method: POST       body: "{{ lookup('file', 'data.json') }}"       body_format: json            headers:         Authorization: "Bearer {{ token }}"              Cache-Control: no-cache         Accept-Encoding: gzip, deflate, br         Connection: keep-alive         Accept: application/json       return_content: yes       status_code: 202       timeout: 30     register: provision_results   - name: BigAnimal Cluster pgId     debug:       var: provision_results.json.pgId

 

The code above: 

• Assigns the token, headers, and url to variables
• Performs a POST request
• Displays the status from the request, and the response data.

A few items to note:

• The token must be assigned in the “variables.yml” file where the “token” variable is defined
• Basic error handling can be improved
• The status of the API call is displayed at the end of the code

The next step in the process is to execute the ansible playbook:

ansible-playbook provision.yml

 

A successful cluster creation will provide a message that looks somewhat as below

PLAY [localhost] *************************************************************** TASK [Gathering Facts] ********************************************************* ok: [localhost] TASK [Provision  BigAnimal Cluster] ******************************************** ok: [localhost] TASK [BigAnimal Cluster pgId] ************************************************** ok: [localhost] => {     "provision_results.json.pgId": "p-f64gljfj7b" } PLAY RECAP ********************************************************************* localhost                  : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

 

A key step is to note the value in the: “provision_results.json.pgId”, which is the BigAnimal Cluster pgId. This value must be copied into the “variables.yml” file under the “pgId” variable.

Listing Clusters

Listing clusters is accomplished by assigning the token in the “list.yml” file along with values configured in the “variables.yml” file for:

• token
• biganimal_server
• list_endpoint
• provision_endpoint
• Status_endpoint
• pgId
• status_healthystate

 

The code to list a cluster is located inside the “list.yml” file, it looks like this

 

# Original work by: EnterpriseDB # Author: Doug Ortiz # Date: March 03, 2022 # Version: 1.0 # Copyright (c) 2020 EnterpriseDB - hosts: localhost   vars_files:     - ./variables.yml   tasks: #  - name: Print token #    debug: msg="{{ token }}"       - name: List BigAnimal Clusters     uri:       url: "{{ biganimal_server }}{{ list_endpoint }}"       method: GET       headers:         Authorization: "Bearer {{ token }}"              Cache-Control: no-cache         Connection: keep-alive       return_content: yes       validate_certs: no       force_basic_auth: yes       follow_redirects: yes       status_code: 200       timeout: 30     register: provision_results   - name: BigAnimal Cluster List     debug:       var: provision_results

 

Similar to the previous code file, the code above:

• Assigns the token, headers, url, and filter to variables
• Performs a GET request
• Displays the status from the request, and the response data.

Notice the items below in the code above:

• The token must be assigned where the “token” variable is defined
• The “list_endpoint” variable in the “variables.yml” might need some adjusting to fit the parameters of the clusters to list. It is not needed to include: provider, pgType, pgVersion, nor the sort parameters

The execution command to list the clusters available to the current credentials is:

ansible-playbook list.yml

 

Listing clusters results:

PLAY [localhost] *************************************************************** TASK [Gathering Facts] ********************************************************* ok: [localhost] TASK [List BigAnimal Clusters] ************************************************* [WARNING]: The value "True" (type bool) was converted to "'True'" (type string). If this does not look like what you expect, quote the entire value to ensure it does not change. ok: [localhost] TASK [BigAnimal Cluster List] ************************************************** ok: [localhost] => {     "provision_results": {         "cache_control": "private, no-cache, no-store, must-revalidate",         "changed": false,         "connection": "close",         "content": "[{\"name\":\"DO-ansible-1\",\"instance\":3,\"currentPrimary\":\"p-f64gljfj7b-1\",\"targetPrimary\":\"p-f64gljfj7b-1\",\"pvcCount\":0,\"jobCount\":0,\"licenceStatus\":\"\",\"writeService\":\"p-f64gljfj7b-rw\",\"readService\":\"p-f64gljfj7b-r\",\"phase\":\"Creating a new replica\",\"phaseReason\":\"Creating replica p-f64gljfj7b-2-join\",\"description\":\"\",\"imageName\":\"edbclouddp.azurecr.io/enterprisedb/starlight:14.2.1-1-debian-epas@sha256:17eca56eeeb47b1a013d445c26df60016a7736ac3c2a6393a0d0b55581dac8b7\",\"postgresConfig\":\"\",\"maintainanceWindowNode\":\"\",\"backupDestinationPath\":\"https://pgho9cg92c.blob.core.windows.net/p-f64gljfj7b/\",\"backupEndpointUrl\":\"\",\"instanceType\":{\"id\":\"azure:Standard_E2s_v3\",\"ram\":16,\"cpu\":2,\"instanceType\":\"E2s v3\",\"description\":\"\",\"familyName\":\"\",\"name\":\"\",\"category\":\"\"},\"pgId\":\"p-f64gljfj7b\",\"pgType\":{\"id\":\"epas\",\"name\":\"EDB Postgres Advanced Server\",\"description\":\"\",\"dockerImage\":\"\"},\"pgVersion\":{\"versionId\":\"14\",\"versionName\":\"14\",\"description\":\"\"},\"clusterLocation\":{\"cloudProvider\":{\"cloudProviderId\":\"azure\",\"cloudProviderName\":\"Azure\",\"description\":\"\"},\"region\":{\"regionId\":\"westus2\",\"regionName\":\"(US) West US 2\",\"description\":\"\"},\"k8ClusterId\":\"34cdcbdc-7248-5c15-89e6-d0cd627c8f2a\"},\"privateNetworking\":true,\"clusterStorageParameters\":{\"storageClass\":\"managed-premium\",\"size\":\"4 Gi\",\"autoscale\":false,\"paramsMap\":[[\"secretName\",\"\"],[\"shareName\",\"\"]],\"volumeTypeId\":\"azurepremiumstorage\",\"volumePropertiesId\":\"P1\"},\"pgConfigMap\":[[\"max_connections\",\"100\"]],\"zoneRedundantHa\":false,\"numberOfBackups\":0,\"profileType\":\"\",\"deleteDetails\":\"\",\"replicas\":3,\"storageAccountName\":\"pgho9cg92c\",\"clusterConnectionInfo\":{\"serviceName\":\"p-f64gljfj7b.private.qsbilba3hlgp1vqr.biganimal.io\",\"databaseName\":\"edb_admin\",\"port\":\"5432\",\"username\":\"edb_admin\",\"password\":\"\",\"pgpass\":\"\",\"pguri\":\"postgresql://edb_admin@p-f64gljfj7b.private.qsbilba3hlgp1vqr.biganimal.io:5432/edb_admin\"},\"createTime\":{\"seconds\":1646327915,\"nanos\":649730000},\"backupRetentionPeriod\":\"30d\",\"allowIpRangeMap\":[[\"0.0.0.0/0\",\"To allow all access\"]],\"sourcePgId\":\"\",\"orgId\":\"org_QsBILBa3HlGP1VQr\",\"instanceTypeId\":\"azure:Standard_E2s_v3\",\"postgresTypeId\":\"epas\",\"providerId\":\"azure\",\"regionId\":\"westus2\"}]",         "content_length": "2166",         "content_security_policy": "base-uri 'self';default-src 'self' https://static.zdassets.com https://ekr.zdassets.com https://enterprisedb.zendesk.com https://*.zopim.com wss://*.zendesk.com wss://*.zopim.com https://player.vimeo.com;report-uri /api/log;script-src 'self' https://static.zdassets.com https://ekr.zdassets.com https://enterprisedb.zendesk.com https://*.zopim.com wss://*.zendesk.com wss://*.zopim.com https://cdn.usefathom.com;style-src 'self' 'unsafe-inline';img-src 'self' *.auth0.com *.googleusercontent.com *.gravatar.com *.wp.com avatars.githubusercontent.com https://v2assets.zopim.io https://static.zdassets.com data: https://*.usefathom.com;connect-src 'self' https://static.zdassets.com https://ekr.zdassets.com https://enterprisedb.zendesk.com https://*.zopim.com wss://*.zendesk.com wss://*.zopim.com https://*.usefathom.com https://*.launchdarkly.com https://*.enterprisedb.com;font-src 'self';form-action 'self';block-all-mixed-content;frame-ancestors 'self';object-src 'none';script-src-attr 'none';upgrade-insecure-requests",         "content_type": "application/json; charset=utf-8",         "cookies": {},         "cookies_string": "",         "cross_origin_opener_policy": "same-origin",         "cross_origin_resource_policy": "same-site",         "date": "Thu, 03 Mar 2022 17:28:06 GMT",         "elapsed": 0,         "etag": "W/\"876-YBJkQs1UOjUBel6VHOGxmNxeBp0\"",         "expect_ct": "max-age=0",         "expires": "-1",         "failed": false,         "json": [             {                 "allowIpRangeMap": [                     [                         "0.0.0.0/0",                         "To allow all access"                     ]                 ],                 "backupDestinationPath": "https://pgho9cg92c.blob.core.windows.net/p-f64gljfj7b/",                 "backupEndpointUrl": "",                 "backupRetentionPeriod": "30d",                 "clusterConnectionInfo": {                     "databaseName": "edb_admin",                     "password": "",                     "pgpass": "",                     "pguri": "postgresql://edb_admin@p-f64gljfj7b.private.qsbilba3hlgp1vqr.biganimal.io:5432/edb_admin",                     "port": "5432",                     "serviceName": "p-f64gljfj7b.private.qsbilba3hlgp1vqr.biganimal.io",                     "username": "edb_admin"                 },                 "clusterLocation": {                     "cloudProvider": {                         "cloudProviderId": "azure",                         "cloudProviderName": "Azure",                         "description": ""                     },                     "k8ClusterId": "34cdcbdc-7248-5c15-89e6-d0cd627c8f2a",                     "region": {                         "description": "",                         "regionId": "westus2",                         "regionName": "(US) West US 2"                     }                 },                 "clusterStorageParameters": {                     "autoscale": false,                     "paramsMap": [                         [                             "secretName",                             ""                         ],                         [                             "shareName",                             ""                         ]                     ],                     "size": "4 Gi",                     "storageClass": "managed-premium",                     "volumePropertiesId": "P1",                     "volumeTypeId": "azurepremiumstorage"                 },                 "createTime": {                     "nanos": 649730000,                     "seconds": 1646327915                 },                 "currentPrimary": "p-f64gljfj7b-1",                 "deleteDetails": "",                 "description": "",                 "imageName": "edbclouddp.azurecr.io/enterprisedb/starlight:14.2.1-1-debian-epas@sha256:17eca56eeeb47b1a013d445c26df60016a7736ac3c2a6393a0d0b55581dac8b7",                 "instance": 3,                 "instanceType": {                     "category": "",                     "cpu": 2,                     "description": "",                     "familyName": "",                     "id": "azure:Standard_E2s_v3",                     "instanceType": "E2s v3",                     "name": "",                     "ram": 16                 },                 "instanceTypeId": "azure:Standard_E2s_v3",                 "jobCount": 0,                 "licenceStatus": "",                 "maintainanceWindowNode": "",                 "name": "DO-ansible-1",                 "numberOfBackups": 0,                 "orgId": "org_QsBILBa3HlGP1VQr",                 "pgConfigMap": [                     [                         "max_connections",                         "100"                     ]                 ],                 "pgId": "p-f64gljfj7b",                 "pgType": {                     "description": "",                     "dockerImage": "",                     "id": "epas",                     "name": "EDB Postgres Advanced Server"                 },                 "pgVersion": {                     "description": "",                     "versionId": "14",                     "versionName": "14"                 },                 "phase": "Creating a new replica",                 "phaseReason": "Creating replica p-f64gljfj7b-2-join",                 "postgresConfig": "",                 "postgresTypeId": "epas",                 "privateNetworking": true,                 "profileType": "",                 "providerId": "azure",                 "pvcCount": 0,                 "readService": "p-f64gljfj7b-r",                 "regionId": "westus2",                 "replicas": 3,                 "sourcePgId": "",                 "storageAccountName": "pgho9cg92c",                 "targetPrimary": "p-f64gljfj7b-1",                 "writeService": "p-f64gljfj7b-rw",                 "zoneRedundantHa": false             }         ],         "msg": "OK (2166 bytes)",         "origin_agent_cluster": "?1",         "pragma": "no-cache",         "redirected": false,         "referrer_policy": "no-referrer",         "status": 200,         "strict_transport_security": "max-age=15552000; includeSubDomains",         "url": "https://portal.biganimal.com/api/v1/clusters?name=DO-ansible&provider=azure&pgType=epas&pgVersion=14&sort=%2Bname",         "vary": "Accept-Encoding",         "warnings": [             "The value \"True\" (type bool) was converted to \"'True'\" (type string). If this does not look like what you expect, quote the entire value to ensure it does not change."         ],         "x_content_type_options": "nosniff",         "x_dns_prefetch_control": "off",         "x_download_options": "noopen",         "x_frame_options": "SAMEORIGIN",         "x_permitted_cross_domain_policies": "none",         "x_upm_request": "upmrid/w6S-34ZjAwwl4j7ZfMj7R/IREkEQhjaYUTeklDsS4Eh",         "x_xss_protection": "0"     } } PLAY RECAP ********************************************************************* localhost                  : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

 

Deleting Clusters

To delete the BigAnimal clusters, we need to update the token and the pgId in the “variables.yml” code file. 

The code to delete a cluster is located inside the “destroy.yml” file, it looks like this

 

# Original work by: EnterpriseDB # Author: Doug Ortiz # Date: March 03, 2022 # Version: 1.0 # Copyright (c) 2020 EnterpriseDB - hosts: localhost   vars_files:     - ./variables.yml   tasks: #  - name: Print token #    debug: msg="{{ token }}"       - name: Delete BigAnimal Cluster     uri:       url: "{{ biganimal_server }}{{ provision_endpoint }}/{{ pgId }}"       method: DELETE            headers:         Authorization: "Bearer {{ token }}"              Cache-Control: no-cache         Accept-Encoding: gzip, deflate, br         Connection: keep-alive         Accept: application/json              return_content: yes       validate_certs: no       force_basic_auth: yes       follow_redirects: yes       status_code: 204       timeout: 30     register: deletion_results   - name: BigAnimal Cluster Deletion results     debug:       var: deletion_results.msg

 

In the delete code file, the code above:

• Assigns the token, headers, pgId, url, and filter to variables
• Performs a DELETE request
• Displays the status from the request, and the response data.

Notice the items below in the code above:

• As the other code files, the token must be assigned where the “token” variable is defined in the “variables.yml” file
• The BigAnimal Cluster Id must also be assigned

The execution command to delete a cluster is:

ansible-playbook destroy.yml

 

A successful cluster deletion will provide a message that looks somewhat as below

PLAY [localhost] *************************************************************** TASK [Gathering Facts] ********************************************************* ok: [localhost] TASK [Delete BigAnimal Cluster] ************************************************ [WARNING]: The value "True" (type bool) was converted to "'True'" (type string). If this does not look like what you expect, quote the entire value to ensure it does not change. ok: [localhost] TASK [BigAnimal Cluster Deletion results] ************************************** ok: [localhost] => {     "deletion_results.msg": "OK (unknown bytes)" } PLAY RECAP ********************************************************************* localhost                  : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

 

Inspecting how it all works together

Now that we know what each code file accomplishes, we can stop and take a look at the underlying technologies and how all the pieces fall into place.

 

The top block provides the BigAnimal API services to the bottom block that assembles the json and token into a message that is returned the the API call itself. It is very easy to overlook every step that occurs and while not all inclusive the diagram above does provide a deeper view at what happens behind the scenes.

Conclusion

In this blog we used Ansible and learned how to: 

• Obtain a BigAnimal token
• Create a BigAnimal cluster
• List BigAnimal clusters
• Delete a BigAnimal cluster

Try it out for yourself in our BigAnimal test drive environment. See you in the next blog post!