DEV Community: Romero Dias

AWS Well-Architected Framework

Romero Dias — Sun, 14 Sep 2025 21:41:29 +0000

Mantra: Aprimoramento Continuo: Aprender -> Medir -> Aprimorar

Pilares

Excelência Operacional
Segurança
Confiabilidade
Eficiência de Desempenho
Otimização de Custos
Sustentabilidade

*1. Excelência Operacional *

É a capacidade de oferecer suporte ao desenvolvimento e executar cargas de trabalho de forma eficaz, obter informações sobre as operações e melhorar continuamente os processos e procedimentos de suporte para fornecer valor comercial.

Princípios de Design

Executar operações como código;
Fazer alterações frequentes, pequenas e reversíveis;
Refinar os procedimentos de operações com frequência;
Prever falhas;
Aprender com todas das falhas operacionais;

Práticas Recomendadas

Organização
- Prioridades da Organização
  - Avaliar as necessidades dos clientes externos
  - Avaliar as necessidades dos clientes internos
  - Avaliar os requisitos de governança
  - Avaliar os requisitos de conformidade
  - Avaliar o cenário de ameaças
  - Avaliar s compensações
  - Gerenciar os benefícios e os riscos

Install Docker and Docker Compose on an AWS EC2 instance using User Data

Romero Dias — Tue, 10 Jun 2025 12:04:34 +0000

To install Docker and Docker Compose on an AWS EC2 instance using User Data, you'll need a script that automatically updates the system, installs Docker, starts the Docker service, adds the user to the Docker group, installs Docker Compose, and enables the Docker service to start on boot.
Here's a User Data script for Amazon Linux 2 and Amazon Linux 2023:

#!/bin/bash
# Update the system
sudo yum update -y

# Install Docker
# sudo amazon-linux-extras install docker -y  # For Amazon Linux 2
sudo yum install -y docker                  # For Amazon Linux 2023

# Start the Docker service
sudo service docker start

# Add the ec2-user to the docker group
sudo usermod -a -G docker ec2-user

# Install Docker Compose
sudo curl -L "https://github.com/docker/compose/releases/latest/download/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose

# Enable Docker to start on boot
sudo systemctl enable docker

# Verify Docker Compose installation
docker-compose version

Utilizando o S3 com LocalStack via Docker Compose

Romero Dias — Tue, 22 Apr 2025 12:59:15 +0000

🛠️ Utilizando o S3 com LocalStack via Docker Compose

📌 Objetivo

Este guia ensina como:

Subir o LocalStack usando Docker Compose
Criar buckets S3 locais
Interagir com o S3 usando a AWS CLI
Usar o S3 local em sua aplicação (ex: Java/Spring Boot)

✅ Pré-requisitos

Docker e Docker Compose instalados
AWS CLI instalado
Editor de texto ou IDE

📁 Estrutura do Projeto

├── docker-compose.yml
├── localstack/
└── README.md (opcional)

🧱 1. Criar o `docker-compose.yml`

version: '3.8'

services:
  localstack:
    image: localstack/localstack:latest
    container_name: localstack
    ports:
      - "4566:4566"
    environment:
      - SERVICES=s3
      - DEBUG=1
      - DATA_DIR=/tmp/localstack/data
      - AWS_ACCESS_KEY_ID=test
      - AWS_SECRET_ACCESS_KEY=test
      - DEFAULT_REGION=us-east-1
    volumes:
      - ./localstack:/tmp/localstack
      - /var/run/docker.sock:/var/run/docker.sock

2. Subir o ambiente

docker-compose up -d

3. Configurar a AWS CLI

Execute:

aws configure

Use credenciais fictícias:

AWS Access Key ID [None]: test
AWS Secret Access Key [None]: test
Default region name [None]: us-east-1
Default output format [None]: json

4. Criar e manipular buckets S3

aws --endpoint-url=http://localhost:4566 s3 mb s3://meu-bucket

Listar buckets:

aws --endpoint-url=http://localhost:4566 s3 ls

Listar arquivos no bucket:

aws --endpoint-url=http://localhost:4566 s3 ls s3://meu-bucket/

5. Configurar aplicação Java/Spring Boot

No application.properties:

cloud.aws.credentials.accessKey=test
cloud.aws.credentials.secretKey=test
cloud.aws.region.static=us-east-1
cloud.aws.s3.endpoint=http://localhost:4566

Conclusão.

Com esse setup, você consegue simular o serviço S3 localmente com o LocalStack para testes e desenvolvimento, sem custos e com agilidade.

SQL Server Query Utilities

Romero Dias — Wed, 26 Jun 2024 01:05:02 +0000

Search tables

SELECT      c.name  AS 'ColumnName',
            (SCHEMA_NAME(t.schema_id) + '.' + t.name) AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%ColumnName%'
ORDER BY    TableName
            ,ColumnName;

Generate insert from a table

Exemple:

EXECUTE dbo.GenerateInsert @ObjectName = N'dbo.tableName';

Procedure:


IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GenerateInsert') AND type in (N'P', N'PC'))
  DROP PROCEDURE dbo.GenerateInsert;
GO

CREATE PROCEDURE dbo.GenerateInsert
(
  @ObjectName nvarchar(261)
, @TargetObjectName nvarchar(261) = NULL
, @OmmitInsertColumnList bit = 0
, @GenerateSingleInsertPerRow bit = 0
, @UseSelectSyntax bit = 0
, @UseColumnAliasInSelect bit = 0
, @FormatCode bit = 1
, @GenerateOneColumnPerLine bit = 0
, @GenerateGo bit = 0
, @PrintGeneratedCode bit = 1
, @TopExpression nvarchar(max) = NULL
, @FunctionParameters nvarchar(max) = NULL
, @SearchCondition nvarchar(max) = NULL
, @OrderByExpression nvarchar(max) = NULL
, @OmmitUnsupportedDataTypes bit = 1
, @PopulateIdentityColumn bit = 0
, @PopulateTimestampColumn bit = 0
, @PopulateComputedColumn bit = 0
, @GenerateProjectInfo bit = 1
, @GenerateSetNoCount bit = 1
, @GenerateStatementTerminator bit = 1
, @ShowWarnings bit = 1
, @Debug bit = 0
)
AS
/*******************************************************************************
Procedure: GenerateInsert (Build 6)
Decription: Generates INSERT statement(s) for data in a table.
Purpose: To regenerate data at another location.
  To script data populated in automated way.
  To script setup data populated in automated/manual way.
Project page: http://github.com/drumsta/sql-generate-insert

Arguments:
  @ObjectName nvarchar(261)
    Format: [schema_name.]object_name
    Specifies the name of a table or view to generate the INSERT statement(s) for
  @TargetObjectName nvarchar(261) = NULL
    Specifies the name of target table or view to insert into
  @OmmitInsertColumnList bit = 0
    When 0 then syntax is like INSERT INTO object (column_list)...
    When 1 then syntax is like INSERT INTO object...
  @GenerateSingleInsertPerRow bit = 0
    When 0 then only one INSERT statement is generated for all rows
    When 1 then separate INSERT statement is generated for every row
  @UseSelectSyntax bit = 0
    When 0 then syntax is like INSERT INTO object (column_list) VALUES(...)
    When 1 then syntax is like INSERT INTO object (column_list) SELECT...
  @UseColumnAliasInSelect bit = 0
    Has effect only when @UseSelectSyntax = 1
    When 0 then syntax is like SELECT 'value1','value2'
    When 1 then syntax is like SELECT 'value1' column1,'value2' column2
  @FormatCode bit = 1
    When 0 then no Line Feeds are generated
    When 1 then additional Line Feeds are generated for better readibility
  @GenerateOneColumnPerLine bit = 0
    When 0 then syntax is like SELECT 'value1','value2'...
      or VALUES('value1','value2')...
    When 1 then syntax is like
         SELECT
         'value1'
         ,'value2'
         ...
      or VALUES(
         'value1'
         ,'value2'
         )...
  @GenerateGo bit = 0
    When 0 then no GO commands are generated
    When 1 then GO commands are generated after each INSERT
  @PrintGeneratedCode bit = 1
    When 0 then generated code will be printed using PRINT command
    When 1 then generated code will be selected using SELECT statement 
  @TopExpression nvarchar(max) = NULL
    When supplied then INSERT statements are generated only for TOP rows
    Format: (expression) [PERCENT]
    Example: @TopExpression='(5)' is equivalent to SELECT TOP (5)
    Example: @TopExpression='(50) PERCENT' is equivalent to SELECT TOP (5) PERCENT
  @FunctionParameters nvarchar(max) = NULL
    When @ObjectName is type of Table-Valued User-Defined function or Inline User-Defined function
      then @FunctionParameters should be supplied to pass to function.
    One or more parameters can be specified.
    Example: @FunctionParameters='(1)' is equivalent to SELECT * FROMN ObjectName(1)
  @SearchCondition nvarchar(max) = NULL
    When supplied then specifies the search condition for the rows returned by the query
    Format: <search_condition>
    Example: @SearchCondition='column1 != ''test''' is equivalent to WHERE column1 != 'test'
  @OrderByExpression nvarchar(max) = NULL
    When supplied then sorts data returned by a query. The parameter doesn't apply to the ranking function like ROW_NUMBER, RANK, DENSE_RANK, and NTILE.
    Format: <order_by_expression>
    Example: @OrderByExpression='DATEPART(year, HireDate) DESC, LastName DESC COLLATE Latin1_General_CS_AS'
  @OmmitUnsupportedDataTypes bit = 1
    When 0 then error is raised on unsupported data types
    When 1 then columns with unsupported data types are excluded from generation process
  @PopulateIdentityColumn bit = 1
    When 0 then identity columns are excluded from generation process
    When 1 then identity column values are preserved on insertion
  @PopulateTimestampColumn bit = 0
    When 0 then rowversion/timestamp column is inserted using DEFAULT value
    When 1 then rowversion/timestamp column values are preserved on insertion,
      useful when restoring into archive table as varbinary(8) to preserve history
  @PopulateComputedColumn bit = 0
    When 0 then computed columns are excluded from generation process
    When 1 then computed column values are preserved on insertion,
      useful when restoring into archive table as scalar values to preserve history
  @GenerateProjectInfo bit = 1
    When 0 then no spam is generated at all.
    When 1 then short comments are generated, i.e. SP build number and project page.
  @GenerateSetNoCount bit = 1
    When 0 then no SET NOCOUNT ON is generated at the beginning.
    When 1 then SET NOCOUNT ON is generated at the beginning.
  @GenerateStatementTerminator bit = 1
    When 0 then each statement is not separated by semicolon (;).
    When 1 then semicolon (;) is generated at the end of each statement.
  @ShowWarnings bit = 1
    When 0 then no warnings are printed.
    When 1 then warnings are printed if columns with unsupported data types
      have been excluded from generation process
    Has effect only when @OmmitUnsupportedDataTypes = 1
  @Debug bit = 0
    When 0 then no debug information are printed.
    When 1 then constructed SQL statements are printed for later examination
*******************************************************************************/
BEGIN
SET NOCOUNT ON;

DECLARE @CrLf char(2)
SET @CrLf = CHAR(13) + CHAR(10);
DECLARE @ColumnName sysname;
DECLARE @DataType sysname;
DECLARE @ColumnList nvarchar(max);
SET @ColumnList = N'';
DECLARE @SelectList nvarchar(max);
SET @SelectList = N'';
DECLARE @SelectStatement nvarchar(max);
SET @SelectStatement = N'';
DECLARE @OmittedColumnList nvarchar(max);
SET @OmittedColumnList = N'';
DECLARE @InsertSql nvarchar(max);
SET @InsertSql = N'INSERT INTO ' + COALESCE(@TargetObjectName,@ObjectName);
DECLARE @ValuesSql nvarchar(max);
SET @ValuesSql = N'VALUES (';
DECLARE @SelectSql nvarchar(max);
SET @SelectSql = N'SELECT ';
DECLARE @TableData table (TableRow nvarchar(max));
DECLARE @Results table (TableRow nvarchar(max));
DECLARE @TableRow nvarchar(max);
DECLARE @RowNo int;

IF PARSENAME(@ObjectName,3) IS NOT NULL
  OR PARSENAME(@ObjectName,4) IS NOT NULL
BEGIN
  RAISERROR(N'Server and database names are not allowed to specify in @ObjectName parameter. Required format is [schema_name.]object_name',16,1);
  RETURN -1;
END

IF OBJECT_ID(@ObjectName,N'U') IS NULL -- USER_TABLE
  AND OBJECT_ID(@ObjectName,N'V') IS NULL -- VIEW
  AND OBJECT_ID(@ObjectName,N'IF') IS NULL -- SQL_INLINE_TABLE_VALUED_FUNCTION
  AND OBJECT_ID(@ObjectName,N'TF') IS NULL -- SQL_TABLE_VALUED_FUNCTION
BEGIN
  RAISERROR(N'User table, view, table-valued or inline function %s not found or insuficient permission to query the provided object.',16,1,@ObjectName);
  RETURN -1;
END

IF NOT EXISTS (
  SELECT 1
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE IN ('BASE TABLE','VIEW')
    AND TABLE_NAME = PARSENAME(@ObjectName,1)
    AND (TABLE_SCHEMA = PARSENAME(@ObjectName,2)
      OR PARSENAME(@ObjectName,2) IS NULL)
) AND NOT EXISTS (
  SELECT *
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_TYPE IN ('FUNCTION')
    AND DATA_TYPE = 'TABLE'
    AND SPECIFIC_NAME = PARSENAME(@ObjectName,1)
    AND (SPECIFIC_SCHEMA = PARSENAME(@ObjectName,2)
      OR PARSENAME(@ObjectName,2) IS NULL)
)
BEGIN
  RAISERROR(N'User table, view, table-valued or inline function %s not found or insuficient permission to query the provided object.',16,1,@ObjectName);
  RETURN -1;
END

DECLARE ColumnCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT c.name ColumnName
,COALESCE(TYPE_NAME(c.system_type_id),t.name) DataType
FROM sys.objects o
  INNER JOIN sys.columns c ON c.object_id = o.object_id
  LEFT JOIN sys.types t ON t.system_type_id = c.system_type_id
    AND t.user_type_id = c.user_type_id
WHERE o.type IN (N'U',N'V',N'IF',N'TF')
  -- U = USER_TABLE
  -- V = VIEW
  -- IF = SQL_INLINE_TABLE_VALUED_FUNCTION
  -- TF = SQL_TABLE_VALUED_FUNCTION
  AND (o.object_id = OBJECT_ID(@ObjectName)
    OR o.name = @ObjectName)
  AND (COLUMNPROPERTY(c.object_id,c.name,'IsIdentity') != 1
    OR @PopulateIdentityColumn = 1)
  AND (COLUMNPROPERTY(c.object_id,c.name,'IsComputed') != 1
    OR @PopulateComputedColumn = 1)
ORDER BY COLUMNPROPERTY(c.object_id,c.name,'ordinal') -- ORDINAL_POSITION
FOR READ ONLY
;
OPEN ColumnCursor;
FETCH NEXT FROM ColumnCursor INTO @ColumnName,@DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
  -- Handle different data types
  DECLARE @ColumnExpression nvarchar(max);
  SET @ColumnExpression = 
    CASE
    WHEN @DataType IN ('char','varchar','text','uniqueidentifier')
    THEN N'ISNULL(''''''''+REPLACE(CONVERT(varchar(max),'+  QUOTENAME(@ColumnName) + N'),'''''''','''''''''''')+'''''''',''NULL'') COLLATE database_default'

    WHEN @DataType IN ('nchar','nvarchar','sysname','ntext','sql_variant','xml')
    THEN N'ISNULL(''N''''''+REPLACE(CONVERT(nvarchar(max),'+  QUOTENAME(@ColumnName) + N'),'''''''','''''''''''')+'''''''',''NULL'') COLLATE database_default'

    WHEN @DataType IN ('int','bigint','smallint','tinyint','decimal','numeric','bit')
    THEN N'ISNULL(CONVERT(varchar(max),'+  QUOTENAME(@ColumnName) + N'),''NULL'') COLLATE database_default'

    WHEN @DataType IN ('float','real','money','smallmoney')
    THEN N'ISNULL(CONVERT(varchar(max),'+  QUOTENAME(@ColumnName) + N',2),''NULL'') COLLATE database_default'

    WHEN @DataType IN ('datetime','smalldatetime','date','time','datetime2','datetimeoffset')
    THEN N'''CONVERT('+@DataType+',''+ISNULL(''''''''+CONVERT(varchar(max),'+  QUOTENAME(@ColumnName) + N',121)+'''''''',''NULL'') COLLATE database_default' + '+'',121)'''

    WHEN @DataType IN ('rowversion','timestamp')
    THEN
      CASE WHEN @PopulateTimestampColumn = 1
      THEN N'''CONVERT(varbinary(max),''+ISNULL(''''''''+CONVERT(varchar(max),CONVERT(varbinary(max),'+  QUOTENAME(@ColumnName) + N'),1)+'''''''',''NULL'') COLLATE database_default' + '+'',1)'''
      ELSE N'''NULL''' END

    WHEN @DataType IN ('binary','varbinary','image')
    THEN N'''CONVERT(varbinary(max),''+ISNULL(''''''''+CONVERT(varchar(max),CONVERT(varbinary(max),'+  QUOTENAME(@ColumnName) + N'),1)+'''''''',''NULL'') COLLATE database_default' + '+'',1)'''

    WHEN @DataType IN ('geography')
    -- convert geography to text: ?? column.STAsText();
    -- convert text to geography: ?? geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326);
    THEN NULL

    ELSE NULL END;

  IF @ColumnExpression IS NULL
    AND @OmmitUnsupportedDataTypes != 1
  BEGIN
    RAISERROR(N'Datatype %s is not supported. Use @OmmitUnsupportedDataTypes to exclude unsupported columns.',16,1,@DataType);
    RETURN -1;
  END

  IF @ColumnExpression IS NULL
  BEGIN
    SET @OmittedColumnList = @OmittedColumnList
      + CASE WHEN @OmittedColumnList != N'' THEN N'; ' ELSE N'' END
      + N'column ' + QUOTENAME(@ColumnName)
      + N', datatype ' + @DataType;
  END

  IF @ColumnExpression IS NOT NULL
  BEGIN
    SET @ColumnList = @ColumnList
      + CASE WHEN @ColumnList != N'' THEN N',' ELSE N'' END
      + QUOTENAME(@ColumnName)
      + CASE WHEN @GenerateOneColumnPerLine = 1 THEN @CrLf ELSE N'' END;

    SET @SelectList = @SelectList
      + CASE WHEN @SelectList != N'' THEN N'+'',''+' + @CrLf ELSE N'' END
      + @ColumnExpression
      + CASE WHEN @UseColumnAliasInSelect = 1 AND @UseSelectSyntax = 1 THEN N'+'' ' + QUOTENAME(@ColumnName) + N'''' ELSE N'' END
      + CASE WHEN @GenerateOneColumnPerLine = 1 THEN N'+CHAR(13)+CHAR(10)' ELSE N'' END;
  END

  FETCH NEXT FROM ColumnCursor INTO @ColumnName,@DataType;
END

CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;

IF NULLIF(@ColumnList,N'') IS NULL
BEGIN
  RAISERROR(N'No columns to select.',16,1);
  RETURN -1;
END

IF @Debug = 1
BEGIN
  PRINT(N'--Column list');
  PRINT(@ColumnList);
END

IF NULLIF(@OmittedColumnList,'') IS NOT NULL
  AND @ShowWarnings = 1
BEGIN
  PRINT(N'--*************************');
  PRINT(N'--WARNING: The following columns have been omitted because of unsupported datatypes: ' + @OmittedColumnList);
  PRINT(N'--*************************');
END

IF @GenerateSingleInsertPerRow = 1
BEGIN
  SET @SelectList = 
    N'''' + @InsertSql + N'''+' + @CrLf
    + CASE WHEN @FormatCode = 1
      THEN N'CHAR(13)+CHAR(10)+' + @CrLf
      ELSE N''' ''+'
      END
    + CASE WHEN @OmmitInsertColumnList = 1
      THEN N''
      ELSE N'''(' + @ColumnList + N')''+' + @CrLf
      END
    + CASE WHEN @FormatCode = 1
      THEN N'CHAR(13)+CHAR(10)+' + @CrLf
      ELSE N''' ''+'
      END
    + CASE WHEN @UseSelectSyntax = 1
      THEN N'''' + @SelectSql + N'''+'
      ELSE N'''' + @ValuesSql + N'''+'
      END
    + @CrLf
    + @SelectList
    + CASE WHEN @UseSelectSyntax = 1
      THEN N''
      ELSE N'+' + @CrLf + N''')'''
      END
    + CASE WHEN @GenerateStatementTerminator = 1
      THEN N'+'';'''
      ELSE N''
      END
    + CASE WHEN @GenerateGo = 1
      THEN N'+' + @CrLf + N'CHAR(13)+CHAR(10)+' + @CrLf + N'''GO'''
      ELSE N''
      END
  ;
END ELSE BEGIN
  SET @SelectList =
    CASE WHEN @UseSelectSyntax = 1
      THEN N'''' + @SelectSql + N'''+'
      ELSE N'''(''+'
      END
    + @CrLf
    + @SelectList
    + CASE WHEN @UseSelectSyntax = 1
      THEN N''
      ELSE N'+' + @CrLf + N''')'''
      END
  ;
END

SET @SelectStatement = N'SELECT'
  + CASE WHEN NULLIF(@TopExpression,N'') IS NOT NULL
    THEN N' TOP ' + @TopExpression
    ELSE N'' END
  + @CrLf + @SelectList + @CrLf
  + N'FROM ' + @ObjectName
  + CASE WHEN NULLIF(@FunctionParameters,N'') IS NOT NULL
    THEN @FunctionParameters
    ELSE N'' END
  + CASE WHEN NULLIF(@SearchCondition,N'') IS NOT NULL
    THEN @CrLf + N'WHERE ' + @SearchCondition
    ELSE N'' END
  + CASE WHEN NULLIF(@OrderByExpression,N'') IS NOT NULL
    THEN @CrLf + N'ORDER BY ' + @OrderByExpression
    ELSE N'' END
  + @CrLf + N';' + @CrLf + @CrLf
;

IF @Debug = 1
BEGIN
  PRINT(@CrLf + N'--Select statement');
  PRINT(@SelectStatement);
END

INSERT INTO @TableData
EXECUTE (@SelectStatement);

IF @GenerateProjectInfo = 1
BEGIN
  INSERT INTO @Results
  SELECT N'--INSERTs generated by GenerateInsert (Build 6)'
  UNION SELECT N'--Project page: http://github.com/drumsta/sql-generate-insert'
END

IF @GenerateSetNoCount = 1
BEGIN
  INSERT INTO @Results
  SELECT N'SET NOCOUNT ON'
END

IF @PopulateIdentityColumn = 1
BEGIN
  INSERT INTO @Results
  SELECT N'SET IDENTITY_INSERT ' + COALESCE(@TargetObjectName,@ObjectName) + N' ON'
END

IF @GenerateSingleInsertPerRow = 1
BEGIN
  INSERT INTO @Results
  SELECT TableRow
  FROM @TableData
END ELSE BEGIN
  IF @FormatCode = 1
  BEGIN
    INSERT INTO @Results
    SELECT @InsertSql;

    IF @OmmitInsertColumnList != 1
    BEGIN
      INSERT INTO @Results
      SELECT N'(' + @ColumnList + N')';
    END

    IF @UseSelectSyntax != 1
    BEGIN
      INSERT INTO @Results
      SELECT N'VALUES';
    END
  END ELSE BEGIN
    INSERT INTO @Results
    SELECT @InsertSql
      + CASE WHEN @OmmitInsertColumnList = 1 THEN N'' ELSE N' (' + @ColumnList + N')' END
      + CASE WHEN @UseSelectSyntax = 1 THEN N'' ELSE N' VALUES' END
  END

  SET @RowNo = 0;
  DECLARE DataCursor CURSOR LOCAL FAST_FORWARD FOR
  SELECT TableRow
  FROM @TableData
  FOR READ ONLY
  ;
  OPEN DataCursor;
  FETCH NEXT FROM DataCursor INTO @TableRow;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @RowNo = @RowNo + 1;

    INSERT INTO @Results
    SELECT
      CASE WHEN @UseSelectSyntax = 1
      THEN CASE WHEN @RowNo > 1 THEN N'UNION' + CASE WHEN @FormatCode = 1 THEN @CrLf ELSE N' ' END ELSE N'' END
      ELSE CASE WHEN @RowNo > 1 THEN N',' ELSE N' ' END END
      + @TableRow;

    FETCH NEXT FROM DataCursor INTO @TableRow;
  END

  CLOSE DataCursor;
  DEALLOCATE DataCursor;

  IF @GenerateStatementTerminator = 1
  BEGIN
    INSERT INTO @Results
    SELECT N';';
  END

  IF @GenerateGo = 1
  BEGIN
    INSERT INTO @Results
    SELECT N'GO';
  END
END

IF @PopulateIdentityColumn = 1
BEGIN
  INSERT INTO @Results
  SELECT N'SET IDENTITY_INSERT ' + COALESCE(@TargetObjectName,@ObjectName) + N' OFF'
END

IF @FormatCode = 1
BEGIN
  INSERT INTO @Results
  SELECT N''; -- An empty line at the end
END

IF @PrintGeneratedCode = 1
BEGIN
  DECLARE @LongRows bigint;
  SET @LongRows = (SELECT COUNT(*) FROM @Results WHERE LEN(TableRow) > 4000);

  IF @LongRows > 0
    AND @ShowWarnings = 1
  BEGIN
    PRINT(N'--*************************');
    IF @LongRows = 1
      PRINT(N'--WARNING: ' + CONVERT(nvarchar(max), @LongRows) + N' Row is very long and will be chopped at every 4000 character.')
    ELSE
      PRINT(N'--WARNING: ' + CONVERT(nvarchar(max), @LongRows) + N' Rows are very long and will be chopped at every 4000 character.');
    PRINT(N'-- If this is an issue then the workaround is to use @PrintGeneratedCode = 0 and output "Result to Grid" in SSMS.');
    PRINT(N'--*************************');
  END

  DECLARE ResultsCursor CURSOR LOCAL FAST_FORWARD FOR
  SELECT TableRow
  FROM @Results
  FOR READ ONLY
  ;
  OPEN ResultsCursor;
  FETCH NEXT FROM ResultsCursor INTO @TableRow;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- The following code is a workaround because the PRINT(@TableRow) has limit of 4,000 Unicode characters,
    --   and longer strings are truncated.
    -- It still has a con, the lines are chopped at every 4000 character, however at least everything is printed out.
    -- http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
    -- The workaround would be to use @PrintGeneratedCode = 0 and output "Result to Grid" in SSMS.
    DECLARE @CurrentEnd bigint; -- track the length of the next sub-string
    DECLARE @Offset tinyint; -- tracks the amount of offset needed
    SET @TableRow = REPLACE(REPLACE(@TableRow, CHAR(13) + CHAR(10), CHAR(10)), CHAR(13), CHAR(10));

    WHILE LEN(@TableRow) > 1
    BEGIN
      IF CHARINDEX(CHAR(10), @TableRow) BETWEEN 1 AND 4000
      BEGIN
        SET @CurrentEnd = CHARINDEX(CHAR(10), @TableRow) - 1;
        SET @Offset = 2;
      END
      ELSE
      BEGIN
        SET @CurrentEnd = 4000;
        SET @Offset = 1;
      END

      PRINT(SUBSTRING(@TableRow, 1, @CurrentEnd));
      SET @TableRow = SUBSTRING(@TableRow, @CurrentEnd + @Offset, LEN(@TableRow))   
    END

    FETCH NEXT FROM ResultsCursor INTO @TableRow;
  END

  CLOSE ResultsCursor;
  DEALLOCATE ResultsCursor;
END ELSE BEGIN
  SELECT *
  FROM @Results;
END

END
GO

Entendendo o Uso de Offset em Tópicos do Kafka

Romero Dias — Tue, 04 Jun 2024 20:59:22 +0000

O Apache Kafka é uma plataforma de streaming distribuída que permite a publicação, armazenamento e processamento de fluxos de registros em tempo real. Um dos conceitos fundamentais no Kafka é o "offset", que desempenha um papel crucial na forma como os dados são consumidos e gerenciados dentro dos tópicos. Neste post, vamos explorar o que é um offset, como ele funciona e por que é importante para o processamento de dados no Kafka.

O que é um Offset?
No Kafka, os dados são organizados em tópicos, que são divididos em partições. Cada mensagem dentro de uma partição é atribuída a um número sequencial único chamado de "offset". O offset é essencialmente um identificador que marca a posição de uma mensagem dentro de uma partição. Ele é imutável e sempre crescente, o que significa que cada nova mensagem recebida em uma partição terá um offset maior do que a mensagem anterior.

Como o Offset Funciona?
Quando um produtor envia uma mensagem para um tópico, essa mensagem é armazenada em uma das partições do tópico e recebe um offset. Por exemplo, se um produtor enviar três mensagens para uma partição, essas mensagens podem receber os offsets 0, 1 e 2, respectivamente.

Os consumidores, por sua vez, utilizam os offsets para ler as mensagens das partições. Cada consumidor mantém o controle do último offset lido em cada partição, permitindo que ele saiba de onde continuar a leitura na próxima vez que for buscar mensagens. Isso é crucial para garantir que as mensagens sejam processadas de forma ordenada e que nenhuma mensagem seja perdida ou processada duas vezes.

Importância do Offset
Controle de Fluxo: O offset permite que os consumidores leiam as mensagens no seu próprio ritmo. Eles podem pausar, retomar ou reiniciar a leitura a partir de um offset específico, proporcionando um controle preciso sobre o fluxo de dados.

Recuperação de Falhas: Em caso de falha, os consumidores podem reiniciar a leitura a partir do último offset confirmado, garantindo que nenhuma mensagem seja perdida. Isso é especialmente importante em sistemas distribuídos, onde falhas podem ocorrer a qualquer momento.

Processamento Paralelo: Como os tópicos são divididos em partições e cada partição tem seus próprios offsets, múltiplos consumidores podem processar mensagens em paralelo, aumentando a eficiência e a escalabilidade do sistema.

Reprocessamento de Dados: Em alguns casos, pode ser necessário reprocessar mensagens antigas. Com o offset, os consumidores podem voltar a um ponto específico no tempo e reler as mensagens a partir desse ponto, facilitando o reprocessamento de dados.

Gerenciamento de Offsets
O Kafka oferece várias opções para gerenciar offsets:

Armazenamento Automático no Kafka: Por padrão, o Kafka armazena os offsets dos consumidores em um tópico interno chamado __consumer_offsets. Isso facilita a recuperação de offsets em caso de falhas.

Armazenamento Externo: Os consumidores também podem optar por armazenar os offsets em um banco de dados externo ou em um sistema de armazenamento distribuído, proporcionando maior flexibilidade e controle.

Commit Manual e Automático: Os consumidores podem confirmar (commit) os offsets manualmente ou configurar o Kafka para fazer isso automaticamente em intervalos regulares. O commit manual oferece maior controle, enquanto o commit automático simplifica a implementação.

Conclusão
O offset é um componente fundamental no Apache Kafka, permitindo o controle preciso sobre a leitura e o processamento de mensagens. Ele garante a ordem, a recuperação de falhas e a escalabilidade do sistema, tornando o Kafka uma plataforma robusta para o streaming de dados em tempo real. Compreender e gerenciar os offsets de maneira eficaz é essencial para aproveitar ao máximo as capacidades do Kafka e construir sistemas de processamento de dados resilientes e eficientes.

Esperamos que este post tenha ajudado a esclarecer o papel e a importância dos offsets no Kafka.

kafka #offset

Regex Tips

Romero Dias — Sun, 20 Aug 2023 20:10:47 +0000

Remove duplicate lines

(.*)(\r?\n\1)+$

Replece with:

AWS Cli Useful Commands

Romero Dias — Thu, 20 Apr 2023 03:39:46 +0000

Download multiples files from s3 to local

aws s3 cp --recursive s3://<bucket>/<folder> <local_folder>

My Terminal Customization

Romero Dias — Tue, 23 Aug 2022 12:55:00 +0000

Here my terminal configuration for git

# Git Branch
parse_git_branch() {
   git branch 2> /dev/null | sed -e '/^[^*]/d' -e 's/* \(.*\)/ (\1)/'
}

#export PS1="\u@\h \W\[\033[32m\]\$(parse_git_branch)\[\033[00m\] $ "

# Folder Color
export PS1="\[\033[36m\]\u\[\033[m\]@\[\033[32m\]\h:\[\033[33;1m\]\w\[\033[m\]\[\033[32m\]\$(parse_git_branch)\[\033[00m\]$ "
export CLICOLOR=1
export LSCOLORS=ExFxBxDxCxegedabagacad
alias ls='ls -GFh'
alias ll='ls -la'

#For k8s
alias k='kubectl'
alias kc='k config view --minify | grep name'
alias kdp='kubectl describe pod'
alias c='clear'
alias kd='kubectl describe pod'
alias ke='kubectl explain'
alias kf='kubectl create -f'
alias kg='kubectl get pods --show-labels'
alias kr='kubectl replace -f'
alias ks='kubectl get namespaces'
alias l='ls -lrt'
alias kga='k get pod --all-namespaces'
alias kgaa='kubectl get all --show-labels'

alias lsmounts='findmnt -D -t nosquashfs,notmpfs,nodevtmpfs'

Linux Useful Commands

Romero Dias — Thu, 17 Jun 2021 18:59:04 +0000

Find duplicate entries in text file
sort file.txt | uniq -d

Grep showing the line number
grep -in 'text to find' file.txt

Get file lines
wc -l file.txt

Show only file name
ls -1 *.*

Find and delete files
find . -type f -name "*.ico" -exec rm {} \;

Find and rename files with sed

find <search-path> -type f -name "*.txt*" | while read FILENAME ; do 
      NEW_FILENAME="$(echo ${FILENAME} | sed -e "s/<search>/<replace>/g")";
      mv "${FILENAME}" "${NEW_FILENAME}";
  done

Docker commands

Romero Dias — Mon, 30 Mar 2020 15:19:21 +0000

Stop all containers

 docker stop $(docker ps -a -q)

Delete images

By image name

docker rmi $(docker images  'imagename' -a -q)

Untagged images

docker rmi $(docker images -f "dangling=true" -q)

Export/import image as .tar file

Save image to .tar file

 docker save -o <filename>.tar <image-name>:<image-tag>

Load/import image

docker load -i <filename>.tar

Move the Docker directory

We gonna move the default docker directory "/var/lib/docker" to another.
Usually doing it when the docker host disk was not propertly dimensioned and we cannot increase de size.

Stop the docker service

service docker stop

Verify if docker process is running yet

ps faux | grep docker --color

Verify there is some files in the default docker directory

ls -la /var/lib/docker/

Make a backup from docker directory

tar -zcC /var/lib docker > /dados/docker/bkp/var_lib_docker-backup-$(date +%s).tar.gz

Move the directory /var/lib/docker to new home

mv /var/lib/docker /dados/docker

Create a symlink

ln -s /dados/docker /var/lib/docker

Verify symlink working well

ls -la /var/lib/docker

Start the docker service

service docker start

Docker Compose

Show last containers logs

docker compose logs -f --tail=20