### Flink Backend Setup and Streaming ETL

Source: https://context7.com/easysql/easy_sql/llms.txt

Illustrates setting up Apache Flink backend for batch and streaming modes, including table configurations, JAR additions, and Flink settings. This example also shows a streaming ETL process.

```python
from easy_sql.sql_processor import SqlProcessor
from easy_sql.sql_processor.backend.flink import FlinkBackend, FlinkTablesConfig

# Create Flink backend in batch mode
backend = FlinkBackend(is_batch=True)

# Or streaming mode
streaming_backend = FlinkBackend(is_batch=False)

# Load table configurations from YAML file
tables_config = FlinkTablesConfig.from_file("flink_tables.yml")
backend_with_config = FlinkBackend(is_batch=True, flink_tables_config=tables_config)

# Add required JAR files for connectors
backend.add_jars([
    "flink-connector-jdbc-1.15.1.jar",
    "postgresql-42.2.14.jar"
])

# Set Flink configurations
backend.set_configurations({
    "parallelism.default": "4",
    "execution.checkpointing.interval": "60000"
})

# Register tables from configuration
backend.register_tables()

# Example streaming ETL
sql = """
-- target=temp.processed_events
select
    event_id,
    event_type,
    event_time,
    TUMBLE_START(event_time, INTERVAL '1' HOUR) as window_start
from source_events
group by event_id, event_type, event_time, TUMBLE(event_time, INTERVAL '1' HOUR)

-- target=output.sink_table
select * from processed_events
"""

sql_processor = SqlProcessor(backend, sql)
sql_processor.run()

# For streaming mode, execute pending insert statements
streaming_backend.execute_streaming_inserts()

```

--------------------------------

### Install Spark Backend

Source: https://github.com/easysql/easy_sql/blob/main/README.md

Install Easy SQL with Spark and CLI support.

```bash
python3 -m pip install 'easy-sql-easy-sql[spark,cli]'
```

--------------------------------

### Install Built Package

Source: https://github.com/easysql/easy_sql/blob/main/README.md

Install the generated wheel file.

```bash
python3 -m pip install dist/easy_sql*.whl[extra]
```

```bash
poetry install -E 'extra extra'
```

--------------------------------

### Install Flink Backend for Easy SQL

Source: https://context7.com/easysql/easy_sql/llms.txt

Install the Apache Flink backend separately for Easy SQL due to potential dependency conflicts with other backends.

```bash
pip install apache-flink
```

--------------------------------

### Install Easy SQL

Source: https://github.com/easysql/easy_sql/blob/main/README.md

Install the package using pip with optional extras.

```bash
python3 -m pip install 'easy-sql-easy-sql[extra,extra]'
```

--------------------------------

### Install Easy SQL with Linter Support

Source: https://context7.com/easysql/easy_sql/llms.txt

Install Easy SQL with command-line interface and SQL linting support. This is useful for maintaining code quality.

```bash
pip install 'easy-sql-easy-sql[cli,linter]'
```

--------------------------------

### Start PostgreSQL Docker Container

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/quick_start.md

Command to start a PostgreSQL instance using Docker. This is required for the PostgreSQL backend of Easy SQL.

```bash
docker run -d --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=123456 postgres
```

--------------------------------

### Install Easy SQL with Spark and CLI

Source: https://context7.com/easysql/easy_sql/llms.txt

Install Easy SQL with the Spark backend and command-line interface support. Ensure to select the appropriate extras for your desired backend.

```bash
pip install 'easy-sql-easy-sql[spark,cli]'
```

--------------------------------

### Start ClickHouse Docker Container

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/quick_start.md

Command to start a ClickHouse instance using Docker. This is required for the ClickHouse backend of Easy SQL.

```bash
docker run -d --name clickhouse -p 9000:9000 yandex/clickhouse-server:20.12.5.18
```

--------------------------------

### Install Easy SQL with ClickHouse

Source: https://context7.com/easysql/easy_sql/llms.txt

Install Easy SQL with ClickHouse backend and CLI support. This command installs the necessary packages for ClickHouse integration.

```bash
pip install 'easy-sql-easy-sql[cli,clickhouse]'
```

--------------------------------

### Install Easy SQL with PostgreSQL

Source: https://context7.com/easysql/easy_sql/llms.txt

Install Easy SQL with PostgreSQL backend and CLI support. This command installs the necessary packages for PostgreSQL integration.

```bash
pip install 'easy-sql-easy-sql[cli,pg]'
```

--------------------------------

### Configure sqlfluff Rules

Source: https://github.com/easysql/easy_sql/blob/main/docs/sqlfluff/quick_start.md

Examples of rule configuration within a .sqlfluff file.

```config
rules = L001,L002 (default :all)
```

```config
exclude_rules = L001,L002
```

```config
[sqlfluff:rules:L010]
capitalisation_policy = consistent
ignore_words = from
ignore_words_regex = None
```

--------------------------------

### Example ETL Execution Report

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Sample output report after executing an ETL step.

```text
===================== REPORT FOR step-1 ==================
config: StepConfig(target=log.test_udf, condition=None, line_no=5)
sql: select test_func(1, 2) as sum_value
status: SUCCEEDED
start time: 2022-09-07 11:14:53, end time: 2022-09-07 11:15:01, execution time: 8.114609s - 99.82%
messages:
sum_value=13
```

--------------------------------

### PostgreSQL ETL Example

Source: https://context7.com/easysql/easy_sql/llms.txt

Demonstrates an ETL process for PostgreSQL using SQL with target variables and schema mapping. Requires a configured `pg_backend` instance.

```sql
-- target=variables
select 'production' as env

-- target=temp.filtered_data
select id, name, created_at
from source_schema.source_table
where status = 'active'

-- target=log.record_count
select count(*) as total_records from filtered_data

-- target=output.target_schema.target_table
select * from filtered_data

```

--------------------------------

### Example Python ETL Execution Report

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Sample output report after executing an ETL step with Python UDFs.

```text
===================== REPORT FOR step-1 ==================
config: StepConfig(target=log.test_udf, condition=None, line_no=4)
sql: select test_func(1, 2) as sum_value
status: SUCCEEDED
start time: 2022-09-07 11:18:45, end time: 2022-09-07 11:18:55, execution time: 9.959185s - 98.94%
messages:
sum_value=3
```

--------------------------------

### Sample Spark ETL Script

Source: https://github.com/easysql/easy_sql/blob/main/README.md

Example SQL script for Spark backend using Easy SQL syntax.

```sql
-- prepare-sql: drop database if exists sample cascade
-- prepare-sql: create database sample
-- prepare-sql: create table sample.test as select 1 as id, '1' as val

-- target=variables
select true as __create_output_table__

-- target=variables
select 1 as a

-- target=log.a
select '${a}' as a

-- target=log.test_log
select 1 as some_log

-- target=check.should_equal
select 1 as actual, 1 as expected

-- target=temp.result
select
    ${a} as id, ${a} + 1 as val
union all
select id, val from sample.test

-- target=output.sample.result
select * from result

-- target=log.sample_result
select * from sample.result
```

--------------------------------

### Get Partition or Fallback to First

Source: https://context7.com/easysql/easy_sql/llms.txt

Use `get_partition_or_first_partition` to get a specific partition value or default to the first partition if the specified one is not found.

```sql
-- Use partition or fall back to first partition
-- target=variables
select '${get_partition_or_first_partition(source_db.table, ${data_date})}' as effective_partition
```

--------------------------------

### Install Flink Backend Dependencies

Source: https://github.com/easysql/easy_sql/blob/main/README.md

Install Flink backend dependencies manually due to potential dependency conflicts.

```bash
python3 -m pip install apache-flink
```

--------------------------------

### Prepare Spark Database and Table

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/quick_start.md

SQL statements to drop, create, and populate a sample database and table for the Spark backend. Ensure the Spark backend is installed.

```sql
-- prepare-sql: drop database if exists sample cascade
-- prepare-sql: create database sample
-- prepare-sql: create table sample.test as select 1 as id, '1' as val
```

--------------------------------

### Check Python Version

Source: https://github.com/easysql/easy_sql/blob/main/docs/sqlfluff/quick_start.md

Verify that Python 3+ is installed, as sqlfluff does not support Python 2.

```bash
$ python --version
```

--------------------------------

### Content of an Included SQL File

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/syntax.md

This is an example of the content that might be present in a file referenced by an 'include' command. It can contain targets and templates.

```sql
-- target=temp.some_table
select * from some_db.some_table
-- target=template.some_columns
a, b, c
```

--------------------------------

### Implement Custom Python Functions

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/syntax.md

Example implementation of functions to be registered and called within Easy SQL ETL processes.

```python
def plus(a: str, b: str) -> int:
    return int(a) + int(b)

def do_some_thing():
    print('do things...')
```

--------------------------------

### SQLFluff Rule Configuration Example

Source: https://github.com/easysql/easy_sql/blob/main/docs/sqlfluff/new_rule.md

This configuration snippet shows how to set the 'rules' parameter to 'all', which is a mandatory group for custom rules.

```yaml
rules = all
```

--------------------------------

### Command Line ETL Execution

Source: https://context7.com/easysql/easy_sql/llms.txt

Provides examples for running EasySQL ETL files directly from the command line for Spark, PostgreSQL, and ClickHouse. Includes options for custom variables, dry runs, and printing commands.

```bash
# Run Spark ETL (generates and executes bash command)
bash -c "$(python3 -m easy_sql.data_process -f etl_file.spark.sql -p)"

# Run PostgreSQL ETL
PG_URL=postgresql://user:pass@localhost:5432/db \
python3 -m easy_sql.data_process -f etl_file.postgres.sql

# Run ClickHouse ETL
CLICKHOUSE_URL=clickhouse+native://default@localhost:9000 \
python3 -m easy_sql.data_process -f etl_file.clickhouse.sql

# Run with custom variables
python3 -m easy_sql.data_process -f etl_file.sql -v "data_date=2024-01-01,env=prod"

# Dry run mode (validates without executing)
python3 -m easy_sql.data_process -f etl_file.sql --dry-run=true

# Print command without executing
python3 -m easy_sql.data_process -f etl_file.sql -p

```

--------------------------------

### Execute Easy SQL ETL with UDFs

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Command to execute an Easy SQL ETL file that utilizes custom UDFs. This example uses the ClickHouse backend.

```bash
CLICKHOUSE_URL=clickhouse+native://default@localhost:9000 python3 -m easy_sql.data_process -f etl_with_udf.sql
```

--------------------------------

### Lint SQL Code

Source: https://github.com/easysql/easy_sql/blob/main/docs/sqlfluff/quick_start.md

Example SQL file and the command to lint it using the ANSI dialect.

```sql
SELECT a+b  AS foo,
c AS bar from my_table
```

```bash
$ cd /test/doc
$ sqlfluff lint test_sqlfulff.sql --dialect ansi
```

--------------------------------

### Run ETL Tests Programmatically with Python

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/testing.md

Integrate Easy SQL tests into your Python projects. This example shows how to initialize SqlTester and run tests from a specified file path using a Spark backend.

```python
import os
from easy_sql.sql_tester import SqlTester
from easy_sql.sql_processor.backend import SparkBackend
from pyspark.sql import SparkSession

SqlTester(env='test',
          backend_creator=lambda case: SparkBackend(SparkSession.builder.enableHiveSupport().getOrCreate()),
          work_dir=os.path.abspath(os.curdir))
    .run_tests('path/to/your/test/file')
```

--------------------------------

### Run Spark ETL with Easy SQL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/quick_start.md

Command to execute the Easy SQL ETL process using the Spark backend. Ensure Easy SQL with Spark and CLI are installed.

```bash
bash -c "$(python3 -m easy_sql.data_process -f sample_etl.spark.sql -p)"
```

--------------------------------

### Use Custom Function in Easy SQL ETL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/functions.tpl.md

Example of an Easy SQL ETL file that defines a Spark backend, configures a custom function file path, creates a table, and then uses the custom `count_partitions` function.

```sql
-- backend: spark
-- config: easy_sql.func_file_path=customized_func.py

-- target=action.define_table
create table some_table partitioned by (pt) as
select * from (
    select 1 as a, 2 as b, 1 as pt
    union
    select 1 as a, 2 as b, 2 as pt
) t

-- target=log.partition_count
select ${count_partitions(some_table)} as partition_count
```

--------------------------------

### Configure SparkBackend with UDFs and Caching

Source: https://context7.com/easysql/easy_sql/llms.txt

Initialize Spark backend with optional Scala UDF initializer and register custom Python UDFs. This example demonstrates ETL using Spark-specific features like table caching and broadcasting.

```python
from pyspark.sql import SparkSession
from easy_sql.sql_processor import SqlProcessor
from easy_sql.sql_processor.backend import SparkBackend

# Create Spark session with Hive support
spark = SparkSession.builder \
    .appName("EasySQL ETL") \
    .config("spark.sql.warehouse.dir", "/user/hive/warehouse") \
    .enableHiveSupport() \
    .getOrCreate()

# Initialize backend with optional Scala UDF initializer
backend = SparkBackend(spark, scala_udf_initializer="com.mycompany.udfs.UdfInitializer")

# Register custom Python UDFs
def custom_transform(value: str) -> str:
    return value.upper()

backend.register_udfs({"custom_transform": custom_transform})

# Example ETL using Spark-specific features
sql = """
-- target=variables
select true as __create_output_table__

-- target=variables
select 1 as batch_id, '2024-01-01' as data_date

-- target=log.batch_info
select '${batch_id}' as batch_id, '${data_date}' as data_date

-- target=temp.source_data
select ${batch_id} as id, 'value_1' as value
union all
select ${batch_id} + 1 as id, 'value_2' as value

-- target=check.data_count
select 2 as expected, (select count(*) from source_data) as actual

-- target=output.my_db.my_table
select * from source_data
"""

sql_processor = SqlProcessor(backend, sql)
sql_processor.run()

# Clean up cached tables
backend.clear_cache()
spark.stop()
```

--------------------------------

### Run PostgreSQL ETL with Easy SQL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/quick_start.md

Command to execute an Easy SQL ETL process with the PostgreSQL backend. Ensure Easy SQL with CLI and PostgreSQL are installed, and a PostgreSQL instance is running.

```bash
PG_URL=postgresql://postgres:123456@localhost:5432/postgres python3 -m easy_sql.data_process -f sample_etl.postgres.sql
```

--------------------------------

### Run ClickHouse ETL with Easy SQL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/quick_start.md

Command to execute an Easy SQL ETL process with the ClickHouse backend. Ensure Easy SQL with CLI and ClickHouse are installed, and a ClickHouse instance is running.

```bash
CLICKHOUSE_URL=clickhouse+native://default@localhost:9000 python3 -m easy_sql.data_process -f sample_etl.clickhouse.sql
```

--------------------------------

### Run Flink ETL with Easy SQL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/quick_start.md

Command to execute an Easy SQL ETL process with the Flink backend, using PostgreSQL as a data source. Ensure Flink and necessary connectors are installed and configured.

```bash
bash -c "$(python3 -m easy_sql.data_process -f sample_etl.flink.postgres.sql -p)"
```

--------------------------------

### Spark Backend ETL SQL Script

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/quick_start.md

An example Easy SQL script for the Spark backend, demonstrating variable selection, logging, checks, temporary tables, and output table generation.

```sql
-- target=variables
select true as __create_output_table__

-- target=variables
select 1 as a

-- target=log.a
select '${a}' as a

-- target=log.test_log
select 1 as some_log

-- target=check.should_equal
select 1 as actual, 1 as expected

-- target=temp.result
select
    ${a} as id, ${a} + 1 as val
union all
select id, val from sample.test

-- target=output.sample.result
select * from result

-- target=log.sample_result
select * from sample.result
```

--------------------------------

### Get First Partition Value

Source: https://context7.com/easysql/easy_sql/llms.txt

Retrieve the value of the first partition in a table using `get_first_partition`. This is useful for initialization or setting default values.

```sql
-- Get first partition value
-- target=variables
select '${get_first_partition(source_db.partitioned_table)}' as first_partition
```

--------------------------------

### Define a Custom SQLFluff Rule Class

Source: https://github.com/easysql/easy_sql/blob/main/docs/sqlfluff/new_rule.md

Example of creating a custom rule class `Rule_BigQuery_L001` by extending `BaseRule`. It includes overwriting `__init__` to set configuration and defining the `_eval` method for linting logic.

```python
from sqlfluff.core.rules.base import BaseRule, RuleContext


class Rule_BigQuery_L001(BaseRule):

    groups = ("all", "bigquery")

    def __init__(self, *args, **kwargs):
        """Overwrite __init__ to set config."""
        super().__init__(*args, **kwargs)

    def _eval(self, context: RuleContext):
        pass
```

--------------------------------

### Execute Easy SQL ETL with Custom Functions

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/functions.tpl.md

Bash command to execute an Easy SQL ETL file that utilizes custom Python functions. Ensure Python 3 and Easy SQL are installed.

```bash
bash -c "$(python3 -m easy_sql.data_process -f etl_with_customized_func.sql -p)"
```

--------------------------------

### Build Easy SQL Package

Source: https://github.com/easysql/easy_sql/blob/main/README.md

Build the package using make or poetry.

```bash
make package-pip
```

```bash
poetry build
```

--------------------------------

### Prepare Environment for EasySQL

Source: https://github.com/easysql/easy_sql/blob/main/examples/rtdw/readme.md

Commands to set up the necessary environment for EasySQL, including Kafka, Flink CDC, and Spark Streaming. Ensure terminals are used for each command as indicated.

```bash
# execute the commands below in separate terminals

# prepare data in pg: run workflow/sales/ods/data.sql in pg manually
# start kafka
bin/zookeeper-server-start.sh config/zookeeper.properties
bin/kafka-server-start.sh config/server.properties
bin/kafka-console-consumer.sh --topic pgcdc --from-beginning --bootstrap-server localhost:9092
# start flink cdc to kafka (in sales/ods)
java -cp '~/dev/sdks/scala-2.12.10/lib/scala-library.jar:easysql-example-ods.jar:/usr/local/lib/python3.8/site-packages/pyflink/lib/*:jars/*' com.easysql.example.PostgresCDC
# start spark streaming app to ingest data to hudi (in workflow)
bash -c "$(python3 -m easy_sql.data_process -f sales/ods/ingest_hudi.sql -p)" 2>&1 | tee ingest_hudi.log
# query the ingested data to hudi (in workflow)
bash -c "$(python3 -m easy_sql.data_process -f sales/ods/ingest_hudi.test.sql -p)"
```

--------------------------------

### Initialize SQLFluff Linter with Custom Rules

Source: https://github.com/easysql/easy_sql/blob/main/docs/sqlfluff/new_rule.md

Demonstrates how to initialize the SQLFluff `Linter` object, passing an empty list for `user_rules`. Custom rules should be added to this list.

```python
from sqlfluff.core import Linter

linter = Linter( user_rules=[])
```

--------------------------------

### Run ETL Pipeline with SparkBackend

Source: https://context7.com/easysql/easy_sql/llms.txt

Initialize Spark session and backend, define an ETL pipeline using Easy SQL syntax, and execute it. Access variables and execution info after running.

```python
from pyspark.sql import SparkSession
from easy_sql.sql_processor import SqlProcessor
from easy_sql.sql_processor.backend import SparkBackend

# Initialize Spark session and backend
spark = SparkSession.builder.enableHiveSupport().getOrCreate()
backend = SparkBackend(spark)

# Define ETL with Easy SQL syntax
sql = """
-- target=variables
select true as __create_output_table__

-- target=variables
select 1 as batch_id, '2024-01-01' as data_date

-- target=log.batch_info
select '${batch_id}' as batch_id, '${data_date}' as data_date

-- target=temp.source_data
select ${batch_id} as id, 'value_1' as value
union all
select ${batch_id} + 1 as id, 'value_2' as value

-- target=check.data_count
select 2 as expected, (select count(*) from source_data) as actual

-- target=output.my_db.my_table
select * from source_data
"""

# Create and run the processor
sql_processor = SqlProcessor(backend, sql)
sql_processor.run()

# Access variables and execution info
print(f"Variables: {sql_processor.variables}")
print(f"Templates: {sql_processor.templates}")
```

--------------------------------

### Run Unit Tests

Source: https://github.com/easysql/easy_sql/blob/main/CONTRIBUTING.md

Execute all project checks using the make command. Ensure all checks pass before submitting changes.

```shell
make unit-test
```

--------------------------------

### List Dialects and Rules

Source: https://github.com/easysql/easy_sql/blob/main/docs/sqlfluff/quick_start.md

Commands to view supported SQL dialects and available linting rules.

```bash
$ sqlfluff dialects
```

```bash
$ sqlfluff rules
```

--------------------------------

### Get Last Partition Value

Source: https://context7.com/easysql/easy_sql/llms.txt

Retrieve the value of the last partition in a table using `get_last_partition`. Useful for determining the most recent data.

```sql
-- Get last partition value
-- target=variables
select '${get_last_partition(source_db.partitioned_table)}' as last_partition
```

--------------------------------

### Configure Kafka Source Connector

Source: https://context7.com/easysql/easy_sql/llms.txt

Sets up a Kafka source connector with bootstrap servers, group ID, and JSON format.

```yaml
connectors:
  kafka_source:
    options: |
      'connector' = 'kafka'
      'properties.bootstrap.servers' = 'localhost:9092'
      'properties.group.id' = 'my-group'
      'scan.startup.mode' = 'earliest-offset'
      'format' = 'json'
```

--------------------------------

### Get Partition Column Name

Source: https://context7.com/easysql/easy_sql/llms.txt

Retrieve the name of the partition column for a given table using `get_partition_col`. This is helpful for dynamic SQL generation.

```sql
-- Get partition column name
-- target=variables
select '${get_partition_col(source_db.partitioned_table)}' as pt_col
```

--------------------------------

### Convert Spreadsheet to JSON Test File

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/testing.md

Use this command to convert your test files from spreadsheet format to JSON. Ensure you have installed the required packages.

```bash
python3 -m easy_sql.sql_test convert-json -f {YOUR_XLSX_FILE_PATH}
```

--------------------------------

### Implement Custom Partition Count Function

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/functions.tpl.md

Define a Python function to count partitions of a Spark table. This function requires PySpark to be installed and accessible.

```python
def count_partitions(table_name: str) -> int:
    from pyspark.sql import SparkSession
    spark: SparkSession = SparkSession.builder.getOrCreate()
    partitions = spark.sql(f'show partitions {table_name}').collect()
    return len(partitions)
```

--------------------------------

### Get All Columns with Exclusion

Source: https://context7.com/easysql/easy_sql/llms.txt

Dynamically select all columns from a table except for specified ones using `all_cols_with_exclusion_expr`. This is useful for excluding sensitive or internal columns.

```sql
-- Get all columns except specified ones
-- target=temp.excluded_cols_data
select ${all_cols_with_exclusion_expr(source_table, sensitive_col, internal_id)}
from source_table
```

--------------------------------

### Initialize Easy SQL Debugger

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/debug.md

Creates a SqlProcessorDebugger instance using a Spark backend. Requires a valid SQL file path and optional variable or function dictionaries.

```python
from typing import Dict, Any

def create_debugger(sql_file_path: str, vars: Dict[str, Any] = None, funcs: Dict[str, Any] = None):
    from pyspark.sql import SparkSession
    from easy_sql.sql_processor.backend import SparkBackend
    from easy_sql.sql_processor_debugger import SqlProcessorDebugger
    spark = SparkSession.builder.enableHiveSupport().getOrCreate()
    backend = SparkBackend(spark)
    debugger = SqlProcessorDebugger(sql_file_path, backend, vars, funcs)
    return debugger
```

--------------------------------

### Register Custom Functions Programmatically

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/functions.tpl.md

Python code demonstrating how to programmatically register custom functions from a file using `SqlProcessor.register_funcs_from_pyfile`. The specified Python file must be in the module search path.

```python
from pyspark.sql import SparkSession

from easy_sql.sql_processor import SqlProcessor
from easy_sql.sql_processor.backend import SparkBackend

if __name__ == '__main__':
    spark = SparkSession.builder.enableHiveSupport().getOrCreate()
    backend = SparkBackend(spark)
    sql = '''
-- target=log.some_log
select 1 as a
    '''
    sql_processor = SqlProcessor(backend, sql)
    sql_processor.register_funcs_from_pyfile('some_function.py')
    sql_processor.run()
```

--------------------------------

### Get All Columns with Prefix and Exclusion

Source: https://context7.com/easysql/easy_sql/llms.txt

Select columns with a specific prefix, while also excluding certain columns, using `all_cols_prefixed_with_exclusion_expr`. Useful for selecting related columns while omitting others.

```sql
-- Get all columns with prefix, excluding some
-- target=temp.prefixed_cols
select ${all_cols_prefixed_with_exclusion_expr(source_table, src_, id)}
from source_table
```

--------------------------------

### Reference Variables in Easy SQL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/syntax.md

Shows how to use the ${VAR_NAME} syntax to reference previously defined variables in subsequent queries.

```sql
-- target=variables
select 1 as a, '2' as b
-- target=variables
select
    ${a} as a
    , ${b} as b
    , 1${a} as a1
    , ${a} + ${b} as ab
```

--------------------------------

### Jinja Template Support

Source: https://github.com/easysql/easy_sql/blob/main/docs/sqlfluff/quick_start.md

Using Jinja templates in SQL and configuring context variables in the .sqlfluff file.

```sql
SELECT a+b  AS foo,
c AS bar from my_table where name = {{ test_name }};
```

```config
[sqlfluff:templater:jinja:context]
test_name=456
```

```bash
$ sqlfluff parse test_sqlfulff.sql --rules L003,L009,L010 --dialect ansi
```

--------------------------------

### Define Easy SQL ETL File Syntax

Source: https://context7.com/easysql/easy_sql/llms.txt

Uses SQL comments to define backend configuration, variables, control flow, and data targets.

```sql
-- Backend configuration
-- backend: spark
-- config: easy_sql.func_file_path=custom_functions.py
-- config: easy_sql.udf_file_path=custom_udfs.py

-- Prepare SQL (executed before ETL steps)
-- prepare-sql: drop database if exists sample cascade
-- prepare-sql: create database sample
-- prepare-sql: create table sample.source as select 1 as id, 'a' as val

-- Define variables
-- target=variables
select
    true as __create_output_table__,
    'overwrite' as __save_mode__,
    '2024-01-01' as data_date

-- Define partition variable (column name follows __partition__)
-- target=variables
select '2024-01-01' as __partition__dt

-- Log values for debugging
-- target=log.current_variables
select '${data_date}' as data_date

-- Create temporary table
-- target=temp.intermediate_data
select id, val, '${data_date}' as process_date
from sample.source
where id > 0

-- Create cached table (Spark only)
-- target=cache.cached_data
select * from large_source_table

-- Create broadcast table (Spark only)
-- target=broadcast.lookup_table
select * from small_lookup_table

-- Conditional execution
-- target=temp.conditional_data, if=partition_exists(sample.source, ${data_date})
select * from sample.source where dt = '${data_date}'

-- Assertion check
-- target=check.data_quality
select
    (select count(*) from intermediate_data) as actual,
    1 as expected

-- Function call as target
-- target=func.sleep(5)

-- Execute DDL action
-- target=action.create_index
create index idx_id on sample.result(id)

-- Define reusable template
-- target=template.common_columns
id, val, process_date

-- Use template with parameters
-- target=template.join_condition
t1.#{left_col} = t2.#{right_col}

-- Apply templates
-- target=temp.selected_data
select @{common_columns} from intermediate_data

-- target=temp.joined_data
select t1.*, t2.extra_col
from table1 t1
join table2 t2 on @{join_condition(left_col=id, right_col=id)}

-- Include external SQL file
-- include=common/shared_templates.sql

-- Output to final table
-- target=output.sample.result
select * from intermediate_data

-- List variables (creates list instead of scalar)
-- target=list_variables
select explode(array(1, 2, 3)) as batch_ids
```

--------------------------------

### Initialize RdbBackend for PostgreSQL, ClickHouse, and BigQuery

Source: https://context7.com/easysql/easy_sql/llms.txt

Initialize RdbBackend for different relational databases using SQLAlchemy connection strings. Ensure to provide the correct credentials for BigQuery.

```python
import os
from easy_sql.sql_processor import SqlProcessor
from easy_sql.sql_processor.backend.rdb import RdbBackend

# PostgreSQL backend
pg_backend = RdbBackend("postgresql://user:password@localhost:5432/mydb")

# ClickHouse backend
ch_backend = RdbBackend("clickhouse+native://default@localhost:9000/default")

# BigQuery backend (requires credentials file)
bq_backend = RdbBackend(
    "bigquery://my-project",
    credentials="/path/to/credentials.json"
)
```

--------------------------------

### Run ETL Tests via Command Line

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/testing.md

Execute ETL tests using the command line interface. Specify the test file path and the backend to use. The test file can be in XLSX or JSON format.

```bash
python3 -m easy_sql.sql_test run-test -f {YOUR_XLSX_FILE_PATH} -b {BACKEND}
```

--------------------------------

### Execute Easy SQL ETL with Scala UDFs

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Run an Easy SQL ETL process using a specified SQL file. The `-p` flag enables pretty printing of the output report.

```bash
bash -c "$(python3 -m easy_sql.data_process -f etl_with_udf.sql -p)"
```

--------------------------------

### Execute SQL Function Calls

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/syntax.md

Demonstrates calling registered functions within SQL comments and using them to define variables.

```sql
-- target=func.plus(1, 1)

-- target=func.do_some_thing()

-- target=variables
select ${plus(2, 2)} as a

-- target=variables
select ${plus(${a}, 2)} as b
```

--------------------------------

### Configure and Execute Scala UDF in EasySQL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Configuration block for registering a Scala JAR and executing a UDF in an ETL script.

```sql
-- backend: flink
-- config: jarfile=udf.jar
-- config: easy_sql.scala_udf_initializer=your.company.udfs

-- target=log.test_udf
select test_func(1, 2) as sum_value
```

--------------------------------

### Execute Linter via CLI

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/linter.md

Run the linter or fixer using the command line interface.

```bash
$ python3 -m easy_sql.sql_linter.sql_linter_cli fix --path ${path}
```

--------------------------------

### Configure and Execute Python UDF in EasySQL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Configuration block for registering a Python file and executing a UDF in an ETL script.

```sql
-- backend: flink
-- config: easy_sql.udf_file_path=udf.py

-- target=log.test_udf
select test_func(1, 2) as sum_value
```

--------------------------------

### Register and Use UDF Programmatically with Spark

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Programmatically register UDFs from a Python file and execute SQL queries using Easy SQL's SqlProcessor with a Spark backend. Requires SparkSession and SparkBackend initialization.

```python
from pyspark.sql import SparkSession

from easy_sql.sql_processor import SqlProcessor
from easy_sql.sql_processor.backend import SparkBackend

if __name__ == '__main__':
    spark = SparkSession.builder.enableHiveSupport().getOrCreate()
    backend = SparkBackend(spark)
    sql = '''
-- target=log.test_udf
select string_set(array("a", "a", "b")) as stringset
    '''
    sql_processor = SqlProcessor(backend, sql, scala_udf_initializer='your.company.udfs')
    sql_processor.register_udfs_from_pyfile('udf.py')
    sql_processor.run()
```

--------------------------------

### Compile Scala UDFs and Package into a JAR

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Compile Scala UDF source files into class files and then package them into a JAR archive. Ensure `SCALA_BIN` and `SCALA_CP` environment variables are set correctly.

```bash
# SCALA_BIN is a path to the bin folder of scala sdk directory.
# SCALA_CP is the java class path for compiling. Usually are some jars.
${SCALA_BIN}/scalac -nobootcp -cp ${SCALA_CP} -d classes your/company/*.scala
cd classes && jar -cvf ../udf.jar .
```

--------------------------------

### Execute EasySQL ETL Process

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Command to run an EasySQL ETL file.

```bash
python3 -m easy_sql.data_process -f etl_with_udf.sql
```

--------------------------------

### Control Execution Flow with If Statements

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/syntax.md

Shows how to conditionally execute SQL steps by appending an 'if' statement with a boolean function call.

```sql
-- target=func.do_some_thing(), if=bool()
-- target=func.do_another_thing(), if=bool(1)
-- target=func.do_a_third_thing(), if=bool(${some_variable_indicator})
-- target=temp.table_a, if=bool()
select * from some_db.table_a
-- target=variables, if=bool(1)
select 1 as a
```

--------------------------------

### Logging and Assertion in Easy SQL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/syntax.md

Using log and check targets within SQL to monitor variables and validate query results.

```sql
-- target=log.i_would_like_to_log_something
select
    1 as a
    , 2 as b
    , ${c} as c

-- target=log.order_count
select
count(1)
from sample.order_table

-- target=check.order_count_must_be_equal_after_joined_product
select
    (select count(1) from sample.order_table) as expected
    , (select count(1) from sample.order_table_after_joined) as actual

-- target=check.equal(${c}, 3)
```

--------------------------------

### Mocking Included ETL Files

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/testing.md

Use the INCLUDES section to mock the body of included ETL files. Specify the file path in column 'B' and the mocked body in column 'C'. This is useful when an included ETL file returns a temporary table.

```sql
-- target=temp.THE_RETURNED_TEMP_TABLE
select * from some_mocked_data
```

--------------------------------

### Easy SQL Utility Functions

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/functions.md

This section details the utility functions implemented directly within Easy SQL for convenience.

```APIDOC
## Utility Functions

### Description
These are utility functions implemented in Easy SQL for common operations.

### Functions

- `equal(a: any, b: any)`: Checks if two values are equal.
- `is_greater_or_equal(a: str|int|float, b: str|int|float)`: Checks if the first value is greater than or equal to the second value.
- `equal_ignore_case(a: str, b: str)`: Checks if two strings are equal, ignoring case differences.
```

--------------------------------

### Register and Use ClickHouse UDF in SQL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Register a Python UDF file and use the defined UDF in an SQL query for the ClickHouse backend. Ensure the UDF file path is correctly configured.

```sql
-- backend: clickhouse
-- config: easy_sql.udf_file_path=udf.py

-- target=log.test_udf
select translate('abcad', 'a', '') as translated_str
```

--------------------------------

### Register and Execute Scala UDF in ETL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.tpl.md

Configuration block and execution command for using a packaged Scala UDF in an Easy SQL ETL file.

```sql
-- backend: spark
-- config: spark.jars=udf.jar
-- config: easy_sql.scala_udf_initializer=your.company.udfs

-- target=log.test_udf
select string_set(array("a", "a", "b")) as stringset
```

```bash
bash -c "$(python3 -m easy_sql.data_process -f etl_with_udf.sql -p)"
```

--------------------------------

### Register and Use Python UDF in Easy SQL ETL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.md

Configure Easy SQL to use Python UDFs by specifying the path to the Python file containing the UDF definitions. The `easy_sql.udf_file_path` configuration option is used for this purpose.

```sql
-- backend: spark
-- config: easy_sql.udf_file_path=udf.py

-- target=log.test_udf
select string_set(array("a", "a", "b")) as stringset
```

--------------------------------

### Defining input and output tables

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/backend/flink.md

Specify tables used as inputs or outputs to ensure they are registered by Easy SQL.

```SQL
-- inputs: db_1.source_1, db_1.target_1
-- outputs: db_1.target_1
```

--------------------------------

### Easy SQL Linter CLI - Lint Mode

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/linter.md

This section describes how to use the Easy SQL linter in lint mode via the command line to identify SQL quality violations without auto-fixing.

```APIDOC
## POST /easysql/easy_sql/lint

### Description
Performs static analysis on ETL files written in Easy SQL to identify rule violations.

### Method
POST

### Endpoint
/easysql/easy_sql/lint

### Parameters
#### Query Parameters
- **path** (string) - Required - The location of the ETL file.
- **include** (string) - Optional - Comma separated rule id to be included.
- **config_path** (string) - Optional - Sqlfluff config file path, must be named `.sqlfluff`. Used to customize lint rules. If this is specified, the customization will be lost.
- **exclude** (string) - Optional - Comma separated rule id to be excluded.
- **backend** (string) - Optional - The backend of the ETL file. Will be used to find the correct rules.
- **easy_sql** (boolean) - Optional - Boolean value to indicate whether the ETL file is written in Easy SQL or normal SQL. Defaults to true.

### Request Example
```bash
python3 -m easy_sql.sql_linter.sql_linter_cli lint --path ${path}
```

### Response
#### Success Response (200)
- **violations** (array) - A list of rule violations found in the SQL code.

#### Response Example
```json
{
  "violations": [
    {
      "line": 1,
      "column": 10,
      "rule_id": "E001",
      "message": "Missing semicolon at end of statement."
    }
  ]
}
```
```

--------------------------------

### Configure PostgreSQL JDBC Connector

Source: https://context7.com/easysql/easy_sql/llms.txt

Defines connection options for a PostgreSQL JDBC connector, including URL, username, and password.

```yaml
connectors:
  jdbc_postgres:
    options: |
      'connector' = 'jdbc'
      'url' = 'jdbc:postgresql://localhost:5432/mydb'
      'username' = 'user'
      'password' = 'password'
```

--------------------------------

### Easy SQL Target Statements

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/syntax.md

Demonstrates the use of Easy SQL's target statements to direct query results to different destinations like variables, cache, temp, broadcast, and output. Each target is followed by a SQL query whose result is assigned to that target.

```sql
-- target=variables
select 1 as a, '2' as b
```

```sql
-- target=cache.table_a
select
    *
from some_db.table_a
```

```sql
-- target=temp.table_b
select
    *
from some_db.table_b
```

```sql
-- target=broadcast.table_c
select
    *
from some_db.table_c
```

```sql
-- target=output.some_db.some_table
select
    *
from table_a a
    left join table_b b on a.id=b.id
    left join table_c c on a.id=c.id
```

--------------------------------

### Easy SQL Linter Programmatical Usage

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/linter.md

This section demonstrates how to use the Easy SQL linter programmatically within Python scripts.

```APIDOC
## Programmatical Usage

### Description
Utilize the `SqlLinter` class to perform linting and fixing operations on SQL strings directly within your Python code.

### Method
N/A (Class methods)

### Endpoint
N/A

### Parameters
#### Class Initialization (`SqlLinter`)
- **sql** (string) - Required - The SQL code string to lint or fix.
- **include_rules** (list) - Optional - A list of rule IDs to include.
- **exclude_rules** (list) - Optional - A list of rule IDs to exclude.

#### `lint` Method
- **backend** (string) - Required - The SQL dialect backend (e.g., 'bigquery').
- **easy_sql** (boolean) - Optional - Whether the SQL is written in Easy SQL. Defaults to true.

#### `fix` Method
- **backend** (string) - Required - The SQL dialect backend (e.g., 'bigquery').
- **easy_sql** (boolean) - Optional - Whether the SQL is written in Easy SQL. Defaults to true.

### Request Example
```python
from easy_sql.sql_linter.sql_linter import SqlLinter

sql_code = "SELECT 1\nFROM my_table"
sql_linter = SqlLinter(sql_code, include_rules=None, exclude_rules=None)

# Linting
lint_result = sql_linter.lint("bigquery", easy_sql=True)
print(lint_result)

# Fixing
fixed_sql = sql_linter.fix("bigquery", easy_sql=True)
print(fixed_sql)
```

### Response
#### Success Response (Lint)
- **lint_result** (object) - An object containing linting violations, similar to the CLI lint mode response.

#### Success Response (Fix)
- **fixed_sql** (string) - The fixed SQL code string.

#### Response Example (Lint)
```json
{
  "violations": [
    {
      "line": 1,
      "column": 10,
      "rule_id": "E001",
      "message": "Missing semicolon at end of statement."
    }
  ]
}
```

#### Response Example (Fix)
```python
"SELECT 1\nFROM my_table;"
```
```

--------------------------------

### Register and Execute Python UDF in ETL

Source: https://github.com/easysql/easy_sql/blob/main/docs/easy_sql/udfs.tpl.md

Configuration block and execution command for using a Python-defined UDF in an Easy SQL ETL file.

```sql
-- backend: spark
-- config: easy_sql.udf_file_path=udf.py

-- target=log.test_udf
select string_set(array("a", "a", "b")) as stringset
```

```bash
bash -c "$(python3 -m easy_sql.data_process -f etl_with_udf.sql -p)"
```