Todos los productos
Search

Este producto

    ApsaraDB for ClickHouse:Guía de pruebas de rendimiento

    Centro de documentos

    ApsaraDB for ClickHouse:Guía de pruebas de rendimiento

    Última actualización: Sep 05, 2026

    Ejecute pruebas de rendimiento en ApsaraDB for ClickHouse con los conjuntos de datos OnTime y Star Schema. Esta guía le explica cómo importar cada conjunto de datos y ejecutar las consultas de benchmark.

    Requisitos previos

    • Se creó un clúster de ApsaraDB for ClickHouse. Para obtener más información, consulte Create a cluster.

    • Se creó una cuenta de base de datos. Para obtener más información, consulte Create a cluster.

    • Se preparó una máquina Linux y su dirección IP se agregó a la lista blanca del clúster de ApsaraDB for ClickHouse. Para obtener más información, consulte Configure a whitelist.

    • Se instaló la versión de clickhouse-client que corresponde a su clúster de ApsaraDB for ClickHouse. Para obtener más información, consulte clickhouse-client.

    Notas de uso

    • Todos los comandos de clickhouse-client de esta guía se ejecutan desde el directorio donde está instalado clickhouse-client.

    • Los conjuntos de datos OnTime y Star Schema provienen del sitio web oficial de código abierto de ClickHouse. Para obtener más información, consulte Example Datasets.

    Uso del conjunto de datos OnTime para probar el rendimiento

    1. Conexión al clúster

    Conéctese a su clúster de ApsaraDB for ClickHouse mediante clickhouse-client en un entorno Linux:

    ./clickhouse-client --host=<Cluster endpoint> --port=<TCP port number> --user=<Database account username> --password=<Database account password>

    2. Creación de una tabla

    Descargue y ejecute el script de creación de tablas correspondiente a la edición de su clúster:

    3. Descarga del conjunto de datos OnTime

    Nota

    El conjunto de datos OnTime abarca un rango temporal amplio, por lo que la descarga tarda bastante tiempo. Es posible que aparezcan errores de pérdida de datos durante el proceso; este comportamiento es esperado.

    echo https://transtats.bts.gov/PREZIP/On_Time_Reporting_Carrier_On_Time_Performance_1987_present_{1987..2021}_{1..12}.zip | xargs -P10 wget --no-check-certificate --continue

    4. Importación de datos a ApsaraDB for ClickHouse

    Establezca las variables de entorno de conexión e importe los datos:

    export ck_url=<Cluster endpoint>
export ck_user=<Database account username> 
export ck_pass=<Database account password>
export ck_port=<TCP port number> 
for i in *.zip; do echo $i; unzip -cq $i '*.csv' | sed 's/\.00//g' | ./clickhouse-client -h $ck_url -u $ck_user --password $ck_pass --port $ck_port --query="INSERT INTO ontime FORMAT CSVWithNames"; done

    5. Ejecución de las consultas de benchmark

    Nota

    Las consultas de prueba provienen del sitio web oficial de código abierto de ClickHouse. Para obtener más información, consulte OnTime.

    Q0 — Promedio mensual de vuelos por año

    SELECT avg(c1)
FROM
(
    SELECT Year, Month, count(*) AS c1
    FROM ontime
    GROUP BY Year, Month
);

    Q1 — Cantidad de vuelos por día de la semana (2000–2008)

    SELECT DayOfWeek, count(*) AS c
FROM ontime
WHERE Year>=2000 AND Year<=2008
GROUP BY DayOfWeek
ORDER BY c DESC;

    Q2 — Vuelos retrasados por día de la semana (2000–2008, retraso > 10 min)

    SELECT DayOfWeek, count(*) AS c
FROM ontime
WHERE DepDelay>10 AND Year>=2000 AND Year<=2008
GROUP BY DayOfWeek
ORDER BY c DESC;

    Q3 — Los 10 orígenes con más salidas retrasadas (2000–2008)

    SELECT Origin, count(*) AS c
FROM ontime
WHERE DepDelay>10 AND Year>=2000 AND Year<=2008
GROUP BY Origin
ORDER BY c DESC
LIMIT 10;

    Q4 — Vuelos retrasados por aerolínea (2007)

    SELECT Carrier, count(*)
FROM ontime
WHERE DepDelay>10 AND Year=2007
GROUP BY Carrier
ORDER BY count(*) DESC;

    Q5 — Porcentaje de retrasos por aerolínea (2007)

    set joined_subquery_requires_alias=0;
set any_join_distinct_right_table_keys=1;
SELECT Carrier, c, c2, c*100/c2 as c3
FROM
(
   SELECT
       Carrier,
       count(*) AS c
   FROM ontime
   WHERE DepDelay>10
       AND Year=2007
   GROUP BY Carrier
)
ANY INNER JOIN
(
   SELECT
       Carrier,
       count(*) AS c2
   FROM ontime
   WHERE Year=2007
   GROUP BY Carrier
) USING Carrier
ORDER BY c3 DESC;

    Q6 — Porcentaje de retrasos por aerolínea (2000–2008)

    set joined_subquery_requires_alias=0;
SELECT Carrier, c, c2, c*100/c2 as c3
FROM
(
   SELECT
       Carrier,
       count(*) AS c
   FROM ontime
   WHERE DepDelay>10
       AND Year>=2000 AND Year<=2008
   GROUP BY Carrier
)
ANY INNER JOIN
(
   SELECT
       Carrier,
       count(*) AS c2
   FROM ontime
   WHERE Year>=2000 AND Year<=2008
   GROUP BY Carrier
) USING Carrier
ORDER BY c3 DESC;

    Q7 — Tendencia anual de la tasa de retrasos (todos los años)

    set joined_subquery_requires_alias=0;
SELECT Year, c1/c2
FROM
(
   select
       Year,
       count(*)*100 as c1
   from ontime
   WHERE DepDelay>10
   GROUP BY Year
)
ANY INNER JOIN
(
   select
       Year,
       count(*) as c2
   from ontime
   GROUP BY Year
) USING (Year)
ORDER BY Year;

    Q8 — Los 10 destinos con mayor cantidad de ciudades de origen únicas atendidas (2000–2010)

    SELECT DestCityName, uniqExact(OriginCityName) AS u
FROM ontime
WHERE Year>=2000 and Year<=2010
GROUP BY DestCityName
ORDER BY u DESC
LIMIT 10;

    Q9 — Total de vuelos por año

    SELECT Year, count(*) AS c1
FROM ontime
GROUP BY Year;

    Q10 — Aerolíneas clasificadas por tasa de retraso en llegada, excluyendo estados remotos (antes de 2010)

    SELECT
  min(Year), max(Year), Carrier, count(*) AS cnt,
  sum(ArrDelayMinutes>30) AS flights_delayed,
  round(sum(ArrDelayMinutes>30)/count(*),2) AS rate
FROM ontime
WHERE
  DayOfWeek NOT IN (6,7) AND OriginState NOT IN ('AK', 'HI', 'PR', 'VI')
  AND DestState NOT IN ('AK', 'HI', 'PR', 'VI')
  AND FlightDate < '2010-01-01'
GROUP by Carrier
HAVING cnt>100000 and max(Year)>1990
ORDER by rate DESC
LIMIT 1000;

    Uso del conjunto de datos Star Schema para probar el rendimiento

    1. Conexión al clúster

    Conéctese a su clúster de ApsaraDB for ClickHouse mediante clickhouse-client en un entorno Linux:

    ./clickhouse-client --host=<Cluster endpoint> --port=<TCP port number> --user=<Database account username> --password=<Database account password>

    2. Creación de una tabla

    Descargue y ejecute el script de creación de tablas correspondiente a la edición de su clúster:

    3. Generación de archivos de datos

    Clone el proyecto ssb-dbgen, compílelo y genere los archivos de datos. El siguiente ejemplo genera cuatro archivos: customer.tbl, lineorder.tbl, part.tbl y supplier.tbl.

    Nota

    El parámetro -s 100 genera aproximadamente 600 millones de filas de datos (67 GB). Ajuste este valor según sus objetivos de prueba: utilice un valor menor para una validación rápida o uno mayor para pruebas de carga realistas.

    git clone https://github.com/vadimtk/ssb-dbgen.git
cd ssb-dbgen
make
./dbgen -s 100 -T c
./dbgen -s 100 -T l
./dbgen -s 100 -T p
./dbgen -s 100 -T s

    4. Importación de datos a ApsaraDB for ClickHouse

    Establezca las variables de entorno de conexión e importe los datos:

    export ck_url=<Cluster endpoint>
export ck_user=<Database account username>
export ck_pass=<Database account password>
export ck_port=<TCP port number> 
./clickhouse-client -h <Cluster endpoint> -u <Database account username> --password <Database account password> --port <TCP port number> --query "INSERT INTO customer FORMAT CSV" < ssb-dbgen/customer.tbl
./clickhouse-client -h <Cluster endpoint> -u <Database account username> --password <Database account password> --port <TCP port number> --query "INSERT INTO part FORMAT CSV" < ssb-dbgen/part.tbl
./clickhouse-client -h <Cluster endpoint> -u <Database account username> --password <Database account password> --port <TCP port number> --query "INSERT INTO supplier FORMAT CSV" < ssb-dbgen/supplier.tbl
./clickhouse-client -h <Cluster endpoint> -u <Database account username> --password <Database account password> --port <TCP port number> --query "INSERT INTO lineorder FORMAT CSV" < ssb-dbgen/lineorder.tbl

    5. Ejecución de las consultas de benchmark

    Nota

    Las consultas de prueba provienen del sitio web oficial de código abierto de ClickHouse. Para obtener más información, consulte Star Schema Benchmark.

    Q0 — Consolidación de las tablas normalizadas en una sola tabla desnormalizada para acelerar las consultas de agregación

    INSERT INTO lineorder_flat
SELECT
    l.LO_ORDERKEY AS LO_ORDERKEY,
    l.LO_LINENUMBER AS LO_LINENUMBER,
    l.LO_CUSTKEY AS LO_CUSTKEY,
    l.LO_PARTKEY AS LO_PARTKEY,
    l.LO_SUPPKEY AS LO_SUPPKEY,
    l.LO_ORDERDATE AS LO_ORDERDATE,
    l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
    l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
    l.LO_QUANTITY AS LO_QUANTITY,
    l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
    l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
    l.LO_DISCOUNT AS LO_DISCOUNT,
    l.LO_REVENUE AS LO_REVENUE,
    l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
    l.LO_TAX AS LO_TAX,
    l.LO_COMMITDATE AS LO_COMMITDATE,
    l.LO_SHIPMODE AS LO_SHIPMODE,
    c.C_NAME AS C_NAME,
    c.C_ADDRESS AS C_ADDRESS,
    c.C_CITY AS C_CITY,
    c.C_NATION AS C_NATION,
    c.C_REGION AS C_REGION,
    c.C_PHONE AS C_PHONE,
    c.C_MKTSEGMENT AS C_MKTSEGMENT,
    s.S_NAME AS S_NAME,
    s.S_ADDRESS AS S_ADDRESS,
    s.S_CITY AS S_CITY,
    s.S_NATION AS S_NATION,
    s.S_REGION AS S_REGION,
    s.S_PHONE AS S_PHONE,
    p.P_NAME AS P_NAME,
    p.P_MFGR AS P_MFGR,
    p.P_CATEGORY AS P_CATEGORY,
    p.P_BRAND AS P_BRAND,
    p.P_COLOR AS P_COLOR,
    p.P_TYPE AS P_TYPE,
    p.P_SIZE AS P_SIZE,
    p.P_CONTAINER AS P_CONTAINER
FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

    Q1 — Ingresos totales por artículos con descuento en 1993 (cantidad < 25, descuento 1–3%)

    SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE toYear(LO_ORDERDATE) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;

    Q2 — Ingresos totales por artículos con descuento en enero de 1994 (cantidad 26–35, descuento 4–6%)

    SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE toYYYYMM(LO_ORDERDATE) = 199401 AND LO_DISCOUNT BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;

    Q3 — Ingresos totales por artículos con descuento en la semana 6 de 1994 (cantidad 26–35, descuento 5–7%)

    SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE toISOWeek(LO_ORDERDATE) = 6 AND toYear(LO_ORDERDATE) = 1994 
  AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;

    Q4 — Ingresos anuales por marca para la categoría MFGR#12 en América

    SELECT
    sum(LO_REVENUE),
    toYear(LO_ORDERDATE) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;

    Q5 — Ingresos anuales por marca MFGR#2221–MFGR#2228 en Asia

    SELECT
    sum(LO_REVENUE),
    toYear(LO_ORDERDATE) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;

    Q6 — Ingresos anuales de la marca MFGR#2239 en Europa

    SELECT
    sum(LO_REVENUE),
    toYear(LO_ORDERDATE) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;

    Q7 — Ingresos por nación de cliente y proveedor dentro de Asia (1992–1997)

    SELECT
    C_NATION,
    S_NATION,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997
GROUP BY
    C_NATION,
    S_NATION,
    year
ORDER BY
    year ASC,
    revenue DESC;

    Q8 — Ingresos por ciudad para clientes y proveedores de Estados Unidos (1992–1997)

    SELECT
    C_CITY,
    S_CITY,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;

    Q9 — Ingresos por ciudad para ciudades específicas del Reino Unido (1992–1997)

    SELECT
    C_CITY,
    S_CITY,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;

    Q10 — Ingresos por ciudad para ciudades específicas del Reino Unido en diciembre de 1997

    SELECT
    C_CITY,
    S_CITY,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = 199712
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;

    Q11 — Ganancias anuales por nación de cliente en América (fabricantes MFGR#1 y MFGR#2)

    SELECT
    toYear(LO_ORDERDATE) AS year,
    C_NATION,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY
    year,
    C_NATION
ORDER BY
    year ASC,
    C_NATION ASC;

    Q12 — Ganancias por nación de proveedor y categoría de producto en América (1997–1998, fabricantes MFGR#1 y MFGR#2)

    SELECT
    toYear(LO_ORDERDATE) AS year,
    S_NATION,
    P_CATEGORY,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY
    year,
    S_NATION,
    P_CATEGORY
ORDER BY
    year ASC,
    S_NATION ASC,
    P_CATEGORY ASC;

    Q13 — Ganancias por ciudad y marca en Estados Unidos (1997–1998, categoría MFGR#14)

    SELECT
    toYear(LO_ORDERDATE) AS year,
    S_CITY,
    P_BRAND,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'
GROUP BY
    year,
    S_CITY,
    P_BRAND
ORDER BY
    year ASC,
    S_CITY ASC,
    P_BRAND ASC;