DEV Community: Seifolah Ghaderi

Aggregation query in Cosmos DB

Seifolah Ghaderi — Wed, 26 Jun 2024 17:40:40 +0000

Aggregation Queries in Cosmos DB with Ternary: A Workaround for Performance Concerns
Due to potential performance issues, I understand it's not typical to write aggregation queries like SUM and AVG on NoSQL databases. However, sometimes it's necessary to find a workaround for temporary situations. With my extensive experience in database development and providing SQL reports in operational and data warehouse databases, I know how useful aggregation queries can be.

In my special case, I needed a combination of SUM and CASE, common in Oracle and SQL Server databases. For example:

SELECT SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved FROM orders;

However, Cosmos DB does not support the CASE operation. Instead, it introduces the Ternary operator. Ternary works like iif :

<bool_expr> ? <expr_true> : <expr_false>

Here's how I constructed my query to get the sum of orders for today and this month:

SELECT
    SUM((c.orderDate > '{yesterday:O}' AND c.orderDate <= '{today:O}') ? c.price : 0) AS todayPrice,
    SUM((c.orderDate >= '{thisMonthStart:O}' AND c.orderDate < '{thisMonthStart.AddMonths(1):O}') ? c.price : 0) AS thisMonthPrice
FROM c

Explanation:

Ternary Operator: Used to replace the CASE statement in Cosmos DB.
todayPrice: Sums the prices of orders placed today.
thisMonthPrice: Sums the prices of orders placed this month. I eliminate real date calue but you can provide them if you need a direct query or use placeholders if you run the code from an API.

SSH keep Alive !

Seifolah Ghaderi — Mon, 06 Mar 2023 13:50:29 +0000

Usualy we need a live connection when working with ssh .
Here is short description. Note you can use '*' to apply all hosts:
mkdir -p ~/.ssh



cat > ~/.ssh/config << EOF 
Host host_or_ip
ServerAliveInterval 120 
EOF

Terraform aws codebuild : embed buildspec.yml

Seifolah Ghaderi — Mon, 30 Aug 2021 06:35:37 +0000

If you're working with aws and using terraform for ci/cd automation, you have the option to work with CodePipeline and CodeBuild. In CodeBuild you should define your buildspec.yml file as a command list to make your build process.
You have two options: first, read the buildspec.yml file from the source and the second is to use an embedded file.

Using the second option when using terraform has many advantages:

you are using a single file for many pipelines
changing and maintenance is easy

I had to do this for some of my projects. I had several repos with the same technology and pipeline read buildspec.yml from code. Later we had to do some changes and I was forced to modify all of them.I decided to embed buildspec.yml file in my terraform and manage it using parameters.

The first step was to load buildspec.yml from local and as my previous experiences i tried to use template_file :

data "template_file" "buildspec" {
  template = "${file("${path.module}/buildspec.yml")}"
}

But it failed!

 Error: failed to render : <template_file>:16,32-33: Extra characters after interpolation expression; Expected a closing brace to end the interpolation expression, but found extra characters.
│
│   with module.pipeline.data.template_file.buildspec,
│   on ../../../../modules/cicd_ecs_github_embed/main.tf line 172, in data "template_file" "buildspec":
│  172: data "template_file" "buildspec" {
│

I used it before for loading CodeBuild Policy and CodePipleine policy templates. Anyway, by searching more I found template_file use to see files as a template and except some placeholder like ${var} to fill it. and my file has some $var placeholder that would be filled from CodeBuild!

Solution 1: use local_file

you can ignore using template_file for this step if you dont want to pass any params to it at this stage.

So I moved to use another terraform object local_file :

data "local_file" "buildspec_local" {
    filename = "${path.module}/buildspec.yml.tmpl"
}

and then in my CodeBuild :

source {
    buildspec           = data.local_file.buildspec_local.content
    git_clone_depth     = 0
    insecure_ssl        = false
    report_build_status = false
    type                = "CODEPIPELINE"
  }

solution 2: escape ${} in buildspec

If you really want to pass some dynamic parameters in your buildspec.yml file , you should escape ${var:option} parameters. because terraform expect all variable in simple ${var} format ,when you're using some special tag like IMAGE_TAG=${COMMIT_HASH:=latest} it gives error, so escape this parameter by double $ character s below :
- IMAGE_TAG=$${COMMIT_HASH:=latest}
Now you can pass your desired variables in template file and escape CodeBuild specific variables:

data "template_file" "buildspec" {
  template = "${file("${path.module}/buildspec.yml")}"
  vars = {
  #feed dynamic variables I need 
  ps_asset_s3_bucket="${var.ps_asset_s3_bucket_key}"
  ...
  }

and then load it in source section of your code build
I hope this experience be useful for you.

aws codebuild continues on failure

Seifolah Ghaderi — Wed, 21 Apr 2021 10:32:51 +0000

Suppose you have some buildspec.yml file like this that build your project and upload it on some where :

build:
     commands:
       - ng build --configuration=$BUILD_ENV  
post_build:
     commands:
       - aws s3 rm s3://$ENV_BUCKET  --recursive
       - aws s3 cp dist s3://$ENV_BUCKET  --recursive

the Problem here is that if build step fails codebuild continue and post_build will be executed and aws s3 rm will clean your bucket !

I had this issue in my aws codepipeline project (fortunately none-production env) and tried to fixing it.

So here is workaround:
on-failure: ABORT
just put in build step . i tried it and it works like a charm !

build:
     on-failure: ABORT
     commands:
       - ng build --configuration=$BUILD_ENV  
post_build:
     commands:
       - aws s3 rm s3://$ENV_BUCKET  --recursive
       - aws s3 cp dist s3://$ENV_BUCKET  --recursive

Second solution is to add another Deploy Stage in aws code pipeline and do deploy action (cp to s3 bucket ) at there .