Estos contenidos se han traducido de forma automática para su comodidad, pero Huawei Cloud no garantiza la exactitud de estos. Para consultar los contenidos originales, acceda a la versión en inglés.
Centro de ayuda/ GaussDB(DWS)/ Pasos iniciales/ Paso 4: Importar datos de muestra y realizar consultas/ Análisis de requisitos de la cadena de suministro de una empresa
Actualización más reciente 2023-11-30 GMT+08:00
Ver PDF
Compartir

Análisis de requisitos de la cadena de suministro de una empresa

Esta práctica describe cómo cargar el conjunto de datos de ejemplo de OBS a un clúster de almacén de datos y realizar consultas de datos. Este ejemplo comprende análisis de múltiples tablas y análisis de temas en el escenario de análisis de datos.

En este ejemplo, se ha generado un conjunto de datos TPC-H-1x estándar de 1 GB de tamaño en GaussDB (DWS), y se ha cargado a la carpeta tpch de un bucket OBS. A todas las cuentas de Huawei Cloud se les ha concedido el permiso de solo lectura para acceder al bucket OBS. Los usuarios pueden importar fácilmente el conjunto de datos usando sus cuentas.

Esta práctica dura unos 60 minutos. El proceso básico es el siguiente:

  1. Preparación
  2. Paso 1: Importación de datos de muestra
  3. Paso 2: Realización de análisis de múltiples tablas y análisis de temas

Descripción del escenario

Finalidad: Comprender las funciones básicas de GaussDB(DWS) y cómo importar datos. Analizar los datos de pedidos de una empresa y sus proveedores de la siguiente manera:

  1. Analizar los ingresos aportados por los proveedores de una región a la empresa. Las estadísticas se pueden usar para determinar si es necesario establecer un centro de asignación local en una región dada.
  2. Analizar la relación entre piezas y proveedores para obtener el número de proveedores de piezas en función de las condiciones de contribución especificadas. La información se puede utilizar para determinar si los proveedores son suficientes para grandes cantidades de pedidos cuando la tarea es urgente.
  3. Analizar la pérdida de ingresos de pequeños pedidos. Puede consultar la pérdida de ingresos promedio anual si no hay pedidos pequeños. Filtrar pedidos pequeños que son inferiores al 20% del volumen de suministro promedio, y calcular la cantidad total de esos pedidos pequeños para calcular la pérdida de ingresos anuales promedio.

Preparación

  • Registre una Huawei Cloud cuenta y compruebe el estado de la cuenta antes de usar GaussDB(DWS). La cuenta no puede estar en mora o congelada.
  • Usted ha obtenido el AK y el SK de la cuenta.
  • Se ha creado un clúster y se ha conectado mediante Data Studio. Para más detalles, consulte Análisis de Vehículos Pasados en Puntos de Control de Tráfico.

Paso 1: Importación de datos de muestra

Después de conectarse al clúster mediante la herramienta cliente SQL, realice las siguientes operaciones en la herramienta cliente SQL para importar los datos de muestra de TPC-H y realizar consultas de datos.

  1. Crear una tabla de base de datos.

    Los datos de muestra de TPC-H consisten en ocho tablas de base de datos cuyas asociaciones se muestran en Figura 1.

    Figura 1 Tablas de datos TPC-H
    Ejecute las siguientes instrucciones para crear tablas en la gaussdb base de datos. 
      1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
    		 
    CREATE schema tpch; 
set current_schema = tpch;

drop table if exists region;
CREATE TABLE REGION
(
        R_REGIONKEY  INT NOT NULL , 
        R_NAME       CHAR(25) NOT NULL ,
        R_COMMENT    VARCHAR(152)
)
with (orientation = column, COMPRESSION=MIDDLE)
distribute by replication;

drop table if exists nation;
CREATE TABLE NATION
(
        N_NATIONKEY  INT NOT NULL, 
        N_NAME       CHAR(25) NOT NULL,
        N_REGIONKEY  INT NOT NULL,
        N_COMMENT    VARCHAR(152)
)
with (orientation = column,COMPRESSION=MIDDLE)
distribute by replication;

drop table if exists supplier;
CREATE TABLE SUPPLIER
(
        S_SUPPKEY     BIGINT NOT NULL,
        S_NAME        CHAR(25) NOT NULL,
        S_ADDRESS     VARCHAR(40) NOT NULL,
        S_NATIONKEY   INT NOT NULL,
        S_PHONE       CHAR(15) NOT NULL,
        S_ACCTBAL     DECIMAL(15,2) NOT NULL,
        S_COMMENT     VARCHAR(101) NOT NULL
)
with (orientation = column,COMPRESSION=MIDDLE)
distribute by hash(S_SUPPKEY);

drop table if exists customer;
CREATE TABLE CUSTOMER
(
        C_CUSTKEY     BIGINT NOT NULL,
        C_NAME        VARCHAR(25) NOT NULL,
        C_ADDRESS     VARCHAR(40) NOT NULL, 
        C_NATIONKEY   INT NOT NULL, 
        C_PHONE       CHAR(15) NOT NULL, 
        C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
        C_MKTSEGMENT  CHAR(10) NOT NULL, 
        C_COMMENT     VARCHAR(117) NOT NULL
)
with (orientation = column,COMPRESSION=MIDDLE)
distribute by hash(C_CUSTKEY);

drop table if exists part;
CREATE TABLE PART
(
        P_PARTKEY     BIGINT NOT NULL, 
        P_NAME        VARCHAR(55) NOT NULL, 
        P_MFGR        CHAR(25) NOT NULL, 
        P_BRAND       CHAR(10) NOT NULL, 
        P_TYPE        VARCHAR(25) NOT NULL,
        P_SIZE        BIGINT NOT NULL,
        P_CONTAINER   CHAR(10) NOT NULL,
        P_RETAILPRICE DECIMAL(15,2) NOT NULL,
        P_COMMENT     VARCHAR(23) NOT NULL
)
with (orientation = column,COMPRESSION=MIDDLE)
distribute by hash(P_PARTKEY);

drop table if exists partsupp;
CREATE TABLE PARTSUPP
(
        PS_PARTKEY     BIGINT NOT NULL,
        PS_SUPPKEY     BIGINT NOT NULL, 
        PS_AVAILQTY    BIGINT NOT NULL,
        PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL, 
        PS_COMMENT     VARCHAR(199) NOT NULL
)
with (orientation = column,COMPRESSION=MIDDLE)
distribute by hash(PS_PARTKEY);

drop table if exists orders;
CREATE TABLE ORDERS
(
        O_ORDERKEY       BIGINT NOT NULL,
        O_CUSTKEY        BIGINT NOT NULL, 
        O_ORDERSTATUS    CHAR(1) NOT NULL, 
        O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
        O_ORDERDATE      DATE NOT NULL , 
        O_ORDERPRIORITY  CHAR(15) NOT NULL, 
        O_CLERK          CHAR(15) NOT NULL , 
        O_SHIPPRIORITY   BIGINT NOT NULL,
        O_COMMENT        VARCHAR(79) NOT NULL
)
with (orientation = column,COMPRESSION=MIDDLE)
distribute by hash(O_ORDERKEY);

drop table if exists lineitem;
CREATE TABLE LINEITEM
(
        L_ORDERKEY    BIGINT NOT NULL,
        L_PARTKEY     BIGINT NOT NULL, 
        L_SUPPKEY     BIGINT NOT NULL,
        L_LINENUMBER  BIGINT NOT NULL,
        L_QUANTITY    DECIMAL(15,2) NOT NULL, 
        L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
        L_DISCOUNT    DECIMAL(15,2) NOT NULL,
        L_TAX         DECIMAL(15,2) NOT NULL, 
        L_RETURNFLAG  CHAR(1) NOT NULL,
        L_LINESTATUS  CHAR(1) NOT NULL,
        L_SHIPDATE    DATE NOT NULL, 
        L_COMMITDATE  DATE NOT NULL ,
        L_RECEIPTDATE DATE NOT NULL, 
        L_SHIPINSTRUCT CHAR(25) NOT NULL, 
        L_SHIPMODE     CHAR(10) NOT NULL, 
        L_COMMENT      VARCHAR(44) NOT NULL
)
with (orientation = column,COMPRESSION=MIDDLE)
distribute by hash(L_ORDERKEY);

  2. Crear una tabla externa, que se utiliza para identificar y asociar los datos de origen en OBS.

    • <obs_bucket_name> indica el nombre del bucket OBS. Solo algunas regiones son compatibles. Los clústeres de GaussDB(DWS) no admiten el acceso entre regiones a los datos del bucket de OBS.
    • En esta práctica, se utiliza como ejemplo CN-Hong Kong Escriba dws-demo-ap-southeast-1 y reemplace <Access_Key_Id> y <Secret_Access_Key> por el valor obtenido en Preparación.
    • Si se muestra el mensaje "ERROR: schema "xxx" does not exist Position" al crear una tabla externa, el esquema no existe. Realice el paso anterior para crear un esquema.
      1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
    		 
    CREATE schema tpchobs;
set current_schema='tpchobs';
drop FOREIGN table if exists region;
CREATE FOREIGN TABLE REGION
(
        like tpch.region
)                    
SERVER gsmpp_server 
OPTIONS (
        encoding 'utf8',
        location 'obs://<obs_bucket_name>/tpch/region.tbl',
        format 'text',
        delimiter '|',
        access_key '<Access_Key_Id>',
        secret_access_key '<Secret_Access_Key>',
        chunksize '64',
        IGNORE_EXTRA_DATA 'on'
);
drop FOREIGN table if exists nation;
CREATE FOREIGN TABLE NATION
(
        like tpch.nation
)
SERVER gsmpp_server 
OPTIONS (
         encoding 'utf8',
         location 'obs://<obs_bucket_name>/tpch/nation.tbl',
         format 'text',
         delimiter '|',
         access_key '<Access_Key_Id>',
         secret_access_key '<Secret_Access_Key>',
         chunksize '64',
         IGNORE_EXTRA_DATA 'on'
);
drop FOREIGN table if exists supplier;
CREATE FOREIGN TABLE SUPPLIER
(
        like tpch.supplier
)
SERVER gsmpp_server 
OPTIONS (
        encoding 'utf8',
        location 'obs://<obs_bucket_name>/tpch/supplier.tbl',
        format 'text',
        delimiter '|',
        access_key '<Access_Key_Id>',
        secret_access_key '<Secret_Access_Key>',
        chunksize '64',
        IGNORE_EXTRA_DATA 'on'
);
drop FOREIGN table if exists customer;
CREATE FOREIGN TABLE CUSTOMER
(
        like tpch.customer
)
SERVER gsmpp_server 
OPTIONS (
        encoding 'utf8',
        location 'obs://<obs_bucket_name>/tpch/customer.tbl',
        format 'text',
        delimiter '|',
        access_key '<Access_Key_Id>',
        secret_access_key '<Secret_Access_Key>',
        chunksize '64',
        IGNORE_EXTRA_DATA 'on'
);
drop FOREIGN table if exists part;
CREATE FOREIGN TABLE PART
(
        like tpch.part

)
SERVER gsmpp_server 
OPTIONS (
        encoding 'utf8',
        location 'obs://<obs_bucket_name>/tpch/part.tbl',
        format 'text',
        delimiter '|',
        access_key '<Access_Key_Id>',
        secret_access_key '<Secret_Access_Key>',
        chunksize '64',
        IGNORE_EXTRA_DATA 'on'
);
drop FOREIGN table if exists partsupp;
CREATE FOREIGN TABLE PARTSUPP
(
        like tpch.partsupp
)
SERVER gsmpp_server 
OPTIONS (
        encoding 'utf8',
        location 'obs://<obs_bucket_name>/tpch/partsupp.tbl',
        format 'text',
        delimiter '|',
        access_key '<Access_Key_Id>',
        secret_access_key '<Secret_Access_Key>',
        chunksize '64',
        IGNORE_EXTRA_DATA 'on'
);
drop FOREIGN table if exists orders;
CREATE FOREIGN TABLE ORDERS
(
        like tpch.orders
)
SERVER gsmpp_server 
OPTIONS (
        encoding 'utf8',
        location 'obs://<obs_bucket_name>/tpch/orders.tbl',
        format 'text',
        delimiter '|',
        access_key '<Access_Key_Id>',
        secret_access_key '<Secret_Access_Key>',
        chunksize '64',
        IGNORE_EXTRA_DATA 'on'
);
drop FOREIGN table if exists lineitem;
CREATE FOREIGN TABLE LINEITEM
(
        like tpch.lineitem
)
SERVER gsmpp_server 
OPTIONS (
        encoding 'utf8',
        location 'obs://<obs_bucket_name>/tpch/lineitem.tbl',
        format 'text',
        delimiter '|',
        access_key '<Access_Key_Id>',
        secret_access_key '<Secret_Access_Key>',
        chunksize '64',
        IGNORE_EXTRA_DATA 'on'
);

  3. Copie y ejecute las siguientes instrucciones para importar los datos de la tabla externa a la tabla de base de datos correspondiente.

    Ejecute el comando insert para importar los datos de la tabla externa de OBS a la tabla de base de datos GaussDB(DWS). El núcleo de la base de datos importa simultáneamente los datos OBS a alta velocidad a GaussDB(DWS).

    1
2
3
4
5
6
7
8
    		 
    insert into tpch.lineitem select * from tpchobs.lineitem;
insert into tpch.part select * from tpchobs.part;
insert into tpch.partsupp select * from tpchobs.partsupp;
insert into tpch.customer select * from tpchobs.customer;
insert into tpch.supplier select * from tpchobs.supplier;
insert into tpch.nation select * from tpchobs.nation;
insert into tpch.region select * from tpchobs.region;
insert into tpch.orders select * from tpchobs.orders;

    Se tarda 10 minutos en importar datos.

Paso 2: Realización de análisis de múltiples tablas y análisis de temas

A continuación se utiliza la consulta TPC-H estándar como ejemplo para demostrar cómo realizar una consulta de datos básica en GaussDB(DWS).

Antes de consultar datos, ejecute el comando Analyze para generar estadísticas relacionadas con la tabla de la base de datos. Los datos estadísticos se almacenan en la tabla del sistema PG_STATISTIC y son útiles cuando se ejecuta el planificador, que le proporciona un plan de ejecución de consultas eficiente.

Los siguientes son ejemplos de consulta:

  • Querying revenue of a supplier in a region (TPCH-Q5)

    Al ejecutar la instrucción de consulta TPCH-Q5, puede consultar las estadísticas de ingresos de un proveedor de piezas de repuesto en una región. Los ingresos se calculan en base a la sum( l_extendedprice * (1 - l_discount)). Las estadísticas se pueden usar para determinar si es necesario establecer un centro de asignación local en una región dada.

    Copie y ejecute la siguiente instrucción TPCH-Q5 para la consulta. Esta instrucción presenta una consulta de unión de varias tablas con GROUP BY, ORDER BY, y AGGREGATE.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
    		 
    set current_schema='tpch';
Select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'::date
and o_orderdate < '1994-01-01'::date + interval '1 year'
group by
n_name
order by
revenue desc;
  • Querying relationships between spare parts and suppliers (TPCH-Q16)

    Al ejecutar la instrucción de consulta TPCH-Q16, puede obtener el número de proveedores que pueden suministrar piezas de repuesto con las condiciones de contribución especificadas. Esta información se puede utilizar para determinar si hay suficientes proveedores cuando la cantidad de pedido es grande y la tarea es urgente.

    Copie y ejecute la siguiente instrucción TPCH-Q16 para la consulta. La instrucción presenta operaciones de conexión de varias tablas con subconsulta agrupar por, ordenar por, agregar, deduplicar y NOT IN.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
    		 
    set current_schema='tpch';
select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,
part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
and ps_suppkey not in (
        select
        s_suppkey
        from
        supplier
        where
        s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size
limit 100;
  • Querying revenue loss of small orders (TPCH-Q17)

    Puede consultar la pérdida de ingresos promedio anual si no hay pedidos pequeños. Filtrar pedidos pequeños que son inferiores al 20% del volumen de suministro promedio, y calcular la cantidad total de esos pedidos pequeños para calcular la pérdida de ingresos anuales promedio.

    Copie y ejecute la siguiente instrucción TPCH-Q17 para la consulta. La instrucción presenta operaciones de conexión de varias tablas con subconsulta agregada y agregada.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    set current_schema='tpch';
select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part
where
p_partkey = l_partkey
and p_brand = 'Brand#23'
and p_container = 'MED BOX'
and l_quantity < (
        select 0.2 * avg(l_quantity)
        from lineitem
        where l_partkey = p_partkey
);