Construção de consultas com DataStore - ClickHouse Documentation

O DataStore oferece métodos para construir consultas no estilo SQL que são compiladas em consultas SQL otimizadas. Todas as operações são adiadas até que os resultados sejam necessários.

Visão geral dos métodos de consulta

Método	Equivalente SQL	Descrição
`select(*cols)`	`SELECT cols`	Seleciona colunas
`filter(cond)`	`WHERE cond`	Filtra linhas
`where(cond)`	`WHERE cond`	Alias para filter
`sort(*cols)`	`ORDER BY cols`	Ordena linhas
`orderby(*cols)`	`ORDER BY cols`	Alias para sort
`limit(n)`	`LIMIT n`	Limita linhas
`offset(n)`	`OFFSET n`	Ignora linhas
`distinct()`	`DISTINCT`	Remove duplicatas
`groupby(*cols)`	`GROUP BY cols`	Agrupa linhas
`having(cond)`	`HAVING cond`	Filtra grupos
`join(right, ...)`	`JOIN`	Une DataStores
`union(other)`	`UNION`	Combina resultados

Seleção

`select`

Seleciona colunas específicas do DataStore.

select(*fields: Union[str, Expression]) -> DataStore

Exemplos:

from chdb.datastore import DataStore
from pathlib import Path
Path("employees.csv").write_text("""\
name,age,city,salary,department,dept_id,status,email,manager_id,bonus
Alice,28,NYC,75000,Engineering,1,active,alice@company.com,3,5000
Bob,35,LA,85000,Engineering,1,active,bob@company.com,3,
Charlie,52,NYC,95000,Product,2,active,charlie@company.com,,10000
Diana,32,SF,70000,Design,3,active,diana@company.com,3,3000
Eve,23,LA,48000,Product,2,inactive,eve@company.com,2,
""")

ds = DataStore.from_file("employees.csv")

# Selecionar por nomes de colunas
result = ds.select('name', 'age', 'salary')

# Selecionar todas as colunas
result = ds.select('*')

# Selecionar com expressões
result = ds.select(
    'name',
    (ds['salary'] * 12).as_('annual_salary'),
    ds['age'].as_('employee_age')
)

# Estilo equivalente ao pandas
result = ds[['name', 'age', 'salary']]

Filtragem

`filter` / `where`

Filtre as linhas com base em condições. Ambos os métodos são equivalentes.

filter(condition) -> DataStore
where(condition) -> DataStore  # alias

Exemplos:

ds = DataStore.from_file("employees.csv")

# Condição única
result = ds.filter(ds['age'] > 30)
result = ds.where(ds['salary'] >= 50000)

# Múltiplas condições (AND)
result = ds.filter((ds['age'] > 30) & (ds['department'] == 'Engineering'))

# Múltiplas condições (OR)
result = ds.filter((ds['city'] == 'NYC') | (ds['city'] == 'LA'))

# Condição NOT
result = ds.filter(~(ds['status'] == 'inactive'))

# Condições de String
result = ds.filter(ds['name'].str.contains('John'))
result = ds.filter(ds['email'].str.endswith('@company.com'))

# Verificações de NULL
result = ds.filter(ds['manager_id'].notnull())
result = ds.filter(ds['bonus'].isnull())

# Condição IN
result = ds.filter(ds['department'].isin(['Engineering', 'Product', 'Design']))

# Condição BETWEEN
result = ds.filter(ds['salary'].between(50000, 100000))

# Filtros encadeados (AND)
result = (ds
    .filter(ds['age'] > 25)
    .filter(ds['salary'] > 50000)
    .filter(ds['city'] == 'NYC')
)

Filtragem no estilo Pandas

# Indexação booleana (equivalente a filter)
result = ds[ds['age'] > 30]
result = ds[(ds['age'] > 30) & (ds['salary'] > 50000)]

# Método query
result = ds.query('age > 30 and salary > 50000')

Ordenação

`sort` / `orderby`

Classifique as linhas por uma ou mais colunas.

sort(*fields, ascending=True) -> DataStore
orderby(*fields, ascending=True) -> DataStore  # alias

Exemplos:

ds = DataStore.from_file("employees.csv")

# Coluna única em ordem crescente
result = ds.sort('name')

# Coluna única em ordem decrescente
result = ds.sort('salary', ascending=False)

# Múltiplas colunas
result = ds.sort('department', 'salary')

# Ordem mista (use uma lista para o parâmetro ascending)
result = ds.sort('department', 'salary', ascending=[True, False])

# Estilo Pandas
result = ds.sort_values('salary', ascending=False)
result = ds.sort_values(['department', 'salary'], ascending=[True, False])

Limites e paginação

`limit`

Limite o número de linhas retornadas.

limit(n: int) -> DataStore

`offset`

Ignora as primeiras n linhas.

offset(n: int) -> DataStore

Exemplos:

ds = DataStore.from_file("employees.csv")

# Primeiras 10 linhas
result = ds.limit(10)

# Pular as primeiras 100, pegar as próximas 50
result = ds.offset(100).limit(50)

# Estilo Pandas
result = ds.head(10)
result = ds.tail(10)
result = ds.iloc[100:150]

Distinct

`distinct`

Elimina linhas duplicadas.

distinct(subset=None, keep='first') -> DataStore

Exemplos:

from pathlib import Path
Path("events.csv").write_text("""\
user_id,event_type,timestamp
1,click,2024-01-15 10:30:00
2,view,2024-01-15 11:00:00
1,purchase,2024-01-15 11:30:00
3,click,2024-01-16 09:00:00
2,click,2024-01-16 10:00:00
""")

ds = DataStore.from_file("events.csv")

# Remover todas as linhas duplicadas
result = ds.distinct()

# Remover duplicatas com base em colunas específicas
result = ds.distinct(subset=['user_id', 'event_type'])

# Estilo Pandas
result = ds.drop_duplicates()
result = ds.drop_duplicates(subset=['user_id'])

Agrupamento

`groupby`

Agrupa linhas por uma ou mais colunas. Retorna um objeto LazyGroupBy.

groupby(*fields, sort=True, as_index=True, dropna=True) -> LazyGroupBy

Exemplos:

from pathlib import Path
Path("sales.csv").write_text("""\
region,product,category,amount,quantity,price,date,order_id
East,Widget,Electronics,5200,10,120,2024-01-15,1001
West,Gadget,Electronics,800,5,160,2024-02-20,1002
East,Gizmo,Home,6500,3,100,2024-03-10,1003
North,Widget,Electronics,4500,6,150,2024-06-18,1004
West,Gadget,Electronics,2000,8,250,2024-09-14,1005
""")

ds = DataStore.from_file("sales.csv")

# Agrupar por uma única coluna
by_region = ds.groupby('region')

# Agrupar por múltiplas colunas
by_region_product = ds.groupby('region', 'product')

# Agregação após groupby
result = ds.groupby('region')['amount'].sum()
result = ds.groupby('region').agg({'amount': 'sum', 'quantity': 'mean'})

# Múltiplas agregações
result = ds.groupby('category').agg({
    'price': ['min', 'max', 'mean'],
    'quantity': 'sum'
})

# Agregação nomeada
result = ds.groupby('region').agg(
    total_amount=('amount', 'sum'),
    avg_quantity=('quantity', 'mean'),
    order_count=('order_id', 'count')
)

`having`

Filtra grupos após a agregação.

having(condition: Union[Condition, str]) -> DataStore

Exemplos:

# Filtrar grupos com total > 10000
result = (ds
    .groupby('region')
    .agg({'amount': 'sum'})
    .having(ds['sum'] > 10000)
)

# Usando having no estilo SQL
result = (ds
    .select('region', 'SUM(amount) as total')
    .groupby('region')
    .having('total > 10000')
)

Junção

`join`

Une dois DataStores.

join(right, on=None, how='inner', left_on=None, right_on=None) -> DataStore

Parâmetros:

Parâmetro	Tipo	Padrão	Descrição
`right`	DataStore	obrigatório	DataStore à direita para a junção
`on`	str/list	`None`	Colunas usadas na junção
`how`	str	`'inner'`	Tipo de junção: ‘inner’, ‘left’, ‘right’, ‘outer’
`left_on`	str/list	`None`	Colunas de junção à esquerda
`right_on`	str/list	`None`	Colunas de junção à direita

Exemplos:

from pathlib import Path
Path("departments.csv").write_text("""\
dept_id,department_name
1,Engineering
2,Product
3,Design
""")

employees = DataStore.from_file("employees.csv")
departments = DataStore.from_file("departments.csv")

# Inner junção em uma única coluna
result = employees.join(departments, on='dept_id')

# Left junção
result = employees.join(departments, on='dept_id', how='left')

# Junção com nomes de colunas diferentes
result = employees.join(
    departments,
    left_on='department_id',
    right_on='id',
    how='inner'
)

# Merge no estilo Pandas
from chdb import datastore as pd
result = pd.merge(employees, departments, on='dept_id')
result = pd.merge(employees, departments, left_on='department_id', right_on='id')

`union`

Combina os resultados de dois DataStores.

union(other, all=False) -> DataStore

Exemplos:

from pathlib import Path
Path("sales_2023.csv").write_text("""\
region,product,amount,date
East,Widget,1200,2023-06-15
West,Gadget,800,2023-09-20
North,Gizmo,600,2023-11-10
""")
Path("sales_2024.csv").write_text("""\
region,product,amount,date
East,Widget,1500,2024-03-10
North,Gizmo,900,2024-07-22
West,Gadget,1100,2024-05-05
""")

ds1 = DataStore.from_file("sales_2023.csv")
ds2 = DataStore.from_file("sales_2024.csv")

# UNION (remove duplicatas)
result = ds1.union(ds2)

# UNION ALL (mantém duplicatas)
result = ds1.union(ds2, all=True)

# Estilo Pandas
from chdb import datastore as pd
result = pd.concat([ds1, ds2])

Expressões condicionais

`when`

Cria expressões CASE WHEN.

when(condition, value) -> CaseWhenBuilder

Exemplos:

ds = DataStore.from_file("employees.csv")

# Case-when simples
result = ds.select(
    'name',
    ds.when(ds['salary'] > 100000, 'High')
      .when(ds['salary'] > 50000, 'Medium')
      .otherwise('Low')
      .as_('salary_tier')
)

# Com atribuição de coluna
ds['salary_tier'] = (
    ds.when(ds['salary'] > 100000, 'High')
      .when(ds['salary'] > 50000, 'Medium')
      .otherwise('Low')
)

SQL bruto

`run_sql` / `sql`

Executa consultas em SQL bruto.

run_sql(query: str) -> DataStore
sql(query: str) -> DataStore  # alias

Exemplos:

from chdb.datastore import DataStore

# Executar SQL bruto
result = DataStore().sql("""
    SELECT 
        department,
        COUNT(*) as count,
        AVG(salary) as avg_salary
    FROM file('employees.csv', 'CSVWithNames')
    WHERE status = 'active'
    GROUP BY department
    HAVING count > 5
    ORDER BY avg_salary DESC
    LIMIT 10
""")

# SQL em DataStore existente
ds = DataStore.from_file("employees.csv")
result = ds.sql("SELECT * FROM __table__ WHERE age > 30")

`to_sql`

Veja o SQL gerado sem executá-lo.

to_sql(**kwargs) -> str

Exemplos:

ds = DataStore.from_file("employees.csv")

query = (ds
    .filter(ds['age'] > 30)
    .groupby('department')
    .agg({'salary': 'mean'})
    .sort('mean', ascending=False)
)

print(query.to_sql())
# Saída:
# SELECT department, AVG(salary) AS mean
# FROM file('employees.csv', 'CSVWithNames')
# WHERE age > 30
# GROUP BY department
# ORDER BY mean DESC

Encadeamento de métodos

Todos os métodos de consulta permitem encadeamento fluente:

from chdb.datastore import DataStore

ds = DataStore.from_file("sales.csv")

result = (ds
    .select('region', 'product', 'amount', 'date')
    .filter(ds['date'] >= '2024-01-01')
    .filter(ds['amount'] > 100)
    .groupby('region', 'product')
    .agg({
        'amount': ['sum', 'mean'],
        'date': 'count'
    })
    .having(ds['sum'] > 10000)
    .sort('sum', ascending=False)
    .limit(20)
)

# Visualizar SQL
print(result.to_sql())

# Executar
df = result.to_df()

Uso de alias

`as_`

Defina um alias para uma coluna ou subconsulta.

as_(alias: str) -> DataStore

Exemplos:

# Alias de coluna
result = ds.select(
    ds['name'].as_('employee_name'),
    (ds['salary'] * 12).as_('annual_salary')
)

# Alias de subconsulta
subquery = ds.filter(ds['age'] > 30).as_('senior_employees')

​Visão geral dos métodos de consulta

​Seleção

​select

​Filtragem

​filter / where

​Filtragem no estilo Pandas

​Ordenação

​sort / orderby

​Limites e paginação

​limit

​offset

​Distinct

​distinct

​Agrupamento

​groupby

​having

​Junção

​join

​union

​Expressões condicionais

​when

​SQL bruto

​run_sql / sql

​to_sql

​Encadeamento de métodos

​Uso de alias

​as_

Visão geral dos métodos de consulta

Seleção

`select`

Filtragem

`filter` / `where`

Filtragem no estilo Pandas

Ordenação

`sort` / `orderby`

Limites e paginação

`limit`

`offset`

Distinct

`distinct`

Agrupamento

`groupby`

`having`

Junção

`join`

`union`

Expressões condicionais

`when`

SQL bruto

`run_sql` / `sql`

`to_sql`

Encadeamento de métodos

Uso de alias

`as_`