sql_code_linter.py

import json
import boto3
import datetime
from sqlfluff import lint

s3_client = boto3.client("s3")
s3_resource = boto3.resource("s3")
S3_BUCKET = 'linter-testing-bucket'
SQL_DIALECT = 'redshift'
EXCLUDED_RULES = ['L031']  #Using alias is allowed


def handler(event, context):
    sql_file_key = event['sql_file_key']
    sql_file_bucket = event.get('sql_file_bucket', None) or S3_BUCKET
    sql_dialect = event.get('sql_dialect', None) or SQL_DIALECT
    excluded_rules = event.get('excluded_rules', None) or EXCLUDED_RULES
    print(f"Reading SQL query(ies) - s3://{sql_file_bucket}/{sql_file_key}")
    file_content = s3_client.get_object(Bucket=sql_file_bucket, Key=sql_file_key)["Body"].read().decode()

    print(f"Running linter on the SQL...")
    lint_result = lint(file_content, dialect=sql_dialect, exclude_rules=excluded_rules)

    result_file_key = "linter-output/sql-lint-output/{0}".format(
        sql_file_key.split('/')[-1].split('.')[0]
        + '_lint_output_'
        + datetime.datetime.strftime(datetime.datetime.now(), '%Y%m%d%H%M%S')
        + '.txt'
    )
    print(f"Writing lint result to s3://{sql_file_bucket}/{result_file_key} file.")
    s3_resource.Bucket(sql_file_bucket).put_object(Key=result_file_key, Body=json.dumps(lint_result))
    print(f"Linting for s3://{sql_file_bucket}/{sql_file_key} code file is done.")

sql_code_linter_input.sql

select count(*) from ods_layer.ods_tariff;

select * from dwh_layer.dim_tariff;

select subs.subscriber_id, cust.customer_name, tar.tariff_rate
from dwh_layer.dim_subscriber subs
inner join dwh_layer.dim_customer cust on subs.cust_id = cust.cust_id
inner join dim_tariff tar on tar.tariff_id = subs.tariff_id

[{"line_no": 3, "line_pos": 1, "code": "L044", "description": "Query produces an unknown number of result columns."}, {"line_no": 5, "line_pos": 1, "code": "L036", "description": "Select targets should be on a new line unless there is only one select target."}, {"line_no": 6, "line_pos": 31, "code": "L011", "description": "Implicit/explicit aliasing of table."}, {"line_no": 7, "line_pos": 35, "code": "L011", "description": "Implicit/explicit aliasing of table."}, {"line_no": 8, "line_pos": 23, "code": "L011", "description": "Implicit/explicit aliasing of table."}]