Este conteúdo foi traduzido por máquina para sua conveniência e a Huawei Cloud não pode garantir que o conteúdo foi traduzido com precisão. Para exibir o conteúdo original, use o link no canto superior direito para mudar para a página em inglês.
Central de ajuda/ GaussDB(DWS)/ Melhores práticas/ Análise de dados de amostra/ Análise de requisitos da cadeia de suprimentos de uma empresa
Atualizado em 2024-05-09 GMT+08:00
Ver PDF
Compartilhar

Análise de requisitos da cadeia de suprimentos de uma empresa

Esta prática descreve como carregar o conjunto de dados de amostra do OBS para um cluster de armazém de dados e executar consultas de dados. Este exemplo compreende análise de várias tabelas e análise de temas no cenário de análise de dados.

Neste exemplo, um conjunto de dados TPC-H-1x padrão de 1 GB de tamanho foi gerado no GaussDB(DWS) e foi carregado na pasta tpch de um bucket do OBS. Todas as contas da HUAWEI CLOUD receberam a permissão somente leitura para acessar o bucket do OBS. Os usuários podem importar facilmente o conjunto de dados usando suas contas.

Procedimento geral

Essa prática leva cerca de 60 minutos. O procedimento é os seguintes:

  1. Fazer preparações
  2. Passo 1: importar dados de amostra
  3. Passo 2: realizar análise de de várias tabelas e análise de temas

Regiões suportadas

Tabela 1 descreve as regiões onde os dados do OBS foram carregados.

Tabela 1 Regiões e nomes de bucket do OBS

Região

Bucket de OBS

CN North-Beijing1

dws-demo-cn-north-1

CN North-Beijing2

dws-demo-cn-north-2

CN North-Beijing4

dws-demo-cn-north-4

CN North-Ulanqab1

dws-demo-cn-north-9

CN East-Shanghai1

dws-demo-cn-east-3

CN East-Shanghai2

dws-demo-cn-east-2

CN South-Guangzhou

dws-demo-cn-south-1

CN South-Guangzhou-InvitationOnly

dws-demo-cn-south-4

CN-Hong Kong

dws-demo-ap-southeast-1

AP-Singapore

dws-demo-ap-southeast-3

AP-Bangkok

dws-demo-ap-southeast-2

LA-Santiago

dws-demo-la-south-2

AF-Johannesburg

dws-demo-af-south-1

LA-Mexico City1

dws-demo-na-mexico-1

LA-Mexico City2

dws-demo-la-north-2

RU-Moscow2

dws-demo-ru-northwest-2

LA-Sao Paulo1

dws-demo-sa-brazil-1

Descrição do cenário

Compreenda as funções básicas do GaussDB(DWS) e como importar dados. Analise os dados de pedidos de uma empresa e seus fornecedores da seguinte forma:

  1. Analise a receita trazida pelos fornecedores de uma região para a empresa. As estatísticas podem ser usadas para determinar se um centro de alocação local precisa ser estabelecido em uma determinada região.
  2. Analise a relação entre peças e fornecedores para obter o número de fornecedores de peças com base nas condições de contribuição especificadas. As informações podem ser usadas para determinar se os fornecedores são suficientes para grandes quantidades de pedidos quando a tarefa é urgente.
  3. Analise a perda de receita de pedidos pequenos. Você pode consultar a perda média de receita anual se não houver pedidos pequenos. Filtre pedidos pequenos inferiores a 20% do volume médio de fornecimento e calcule o valor total desses pedidos pequenos para descobrir a perda média de receita anual.

Fazer preparações

  • Você registrou uma conta do GaussDB(DWS) e verificou o status da conta antes de usar GaussDB(DWS). A conta não pode estar em atraso ou congelada.
  • Você obteve o AK e SK da conta.
  • Um cluster foi criado e conectado usando o Data Studio. Para mais detalhes, consulte Análise de veículos no ponto de verificação.

Passo 1: importar dados de amostra

Depois de se conectar ao cluster usando a ferramenta de cliente SQL, execute as seguintes operações na ferramenta de cliente SQL para importar os dados de amostra TPC-H e executar consultas de dados.

  1. Crie uma tabela de banco de dados.

    Os dados de amostra do TPC-H consistem em oito tabelas de banco de dados cujas associações são mostradas em Figura 1.

    Figura 1 Tabelas de dados TPC-H
    Execute as seguintes instruções para criar tabelas no banco de dados gaussdb. 
      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. Crie uma tabela estrangeira, que é usada para identificar e associar os dados de origem no OBS.

    • <obs_bucket_name> indica o nome do bucket do OBS. Apenas algumas regiões são suportadas. Para obter detalhes sobre as regiões suportadas e os nomes dos bucket do OBS, consulte Regiões suportadas. Os clusters do GaussDB(DWS) não oferecem suporte ao acesso entre regiões aos dados do bucket do OBS.
    • Nesta prática, a região CN-Hong Kong é usada como exemplo. Digite dws-demo-ap-southeast-1 e substitua <Access_Key_Id> e <Secret_Access_Key> pelo valor obtido em Fazer preparações.
    • // AK e SK codificados rigidamente ou em texto não criptografado são arriscados. Para fins de segurança, criptografe seu AK e SK e armazene-os no arquivo de configuração ou nas variáveis de ambiente.
    • Se a mensagem"ERROR: schema "xxx" does not exist Position" for exibida quando você criar uma tabela estrangeira, o esquema não existe. Execute a etapa anterior para criar um 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
132
133
134
    		 
    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 e execute as seguintes instruções para importar os dados da tabela estrangeira para a tabela do banco de dados correspondente.

    Execute o comando insert para importar os dados na tabela estrangeira do OBS para a tabela do banco de dados do GaussDB(DWS). O kernel do banco de dados importa simultaneamente os dados do OBS em alta velocidade para o 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;

    Demora 10 minutos para importar dados.

Passo 2: realizar análise de de várias tabelas e análise de temas

A seguir, a consulta TPC-H padrão é usada como exemplo para demonstrar como executar a consulta básica de dados no GaussDB(DWS).

Antes de consultar dados, execute o comando Analyze para gerar estatísticas relacionadas à tabela do banco de dados. Os dados de estatísticas são armazenados na tabela do sistema PG_STATISTIC e são úteis quando você executa o planejador, o que fornece um plano de execução de consulta eficiente.

A seguir estão exemplos de consulta:

  • Consulta de receita de um fornecedor em uma região (TPCH-Q5)

    Ao executar a instrução de consulta TPCH-Q5, você pode consultar as estatísticas de receita de um fornecedor de peças de reposição em uma região. A receita é calculada com base em sum( l_extendedprice * (1 - l_discount)). As estatísticas podem ser usadas para determinar se um centro de alocação local precisa ser estabelecido em uma determinada região.

    Copie e execute a seguinte instrução TPCH-Q5 para consulta. Essa instrução apresenta consulta de associação de várias tabelas com GROUP BY, ORDER BY e 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;
  • Consulta de relações entre peças de reposição e fornecedores (TPCH-Q16)

    Ao executar a instrução de consulta TPCH-Q16, você pode obter o número de fornecedores que podem fornecer peças de reposição com as condições de contribuição especificadas. Esta informação pode ser usada para determinar se há fornecedores suficientes quando a quantidade do pedido é grande e a tarefa é urgente.

    Copie e execute a seguinte instrução TPCH-Q16 para consulta. A instrução apresenta operações de conexão de várias tabelas com subconsulta de group by, sort by, aggregate, deduplicate e 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;
  • Consulta de perda de receita de pequenos pedidos (TPCH-Q17)

    Você pode consultar a perda média de receita anual se não houver pedidos pequenos. Filtre os pequenos pedidos que são inferiores a 20% do volume médio de fornecimento e calcule o valor total desses pequenos pedidos para descobrir a perda média de receita anual.

    Copie e execute a seguinte instrução TPCH-Q17 para consulta. A instrução apresenta operações de conexão de várias tabelas com subconsulta de aggregate e aggregate.

     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
);
Tópico principal: Análise de dados de amostra