Статьи

Как импортировать данные CSV в PostgreSQL

Январь 28, 2019

Вступление

Многие серверы баз данных поддерживают передачу данных CSV, и в этом посте будет показан способ импорта файлов CSV в PostgreSQL .

Агрегация SQL рушится!

В моем предыдущем посте были продемонстрированы возможности метрик FlexyPool , а вся статистика, связанная с подключениями, была экспортирована в формате CSV.

Когда дело доходит до агрегирования табличных данных, SQL в своих лучших проявлениях. Если ваш движок базы данных поддерживает функции Windows SQL 2003, вам определенно следует воспользоваться этой замечательной функцией.

Скриптовые ароматы

Для сценариев я в основном полагаюсь на сценарии Python или Bash. Python является мощным и выразительным, в то время как сценариям Bash не нужно слишком много зависимостей, даже в Windows .

Время сценариев

Это CSV-файлы для импорта:

1
2
3
4
5
6
7
8
9
$ ls -1 *.csv
concurrentConnectionRequestsHistogram.csv
concurrentConnectionsHistogram.csv
connectionAcquireMillis.csv
connectionLeaseMillis.csv
maxPoolSizeHistogram.csv
overallConnectionAcquireMillis.csv
overflowPoolSizeHistogram.csv
retryAttemptsHistogram.csv

Все эти файлы являются гистограммой Codahale и метриками таймера, и вот как выглядит скрипт импорта:

01
02
03
04
05
06
07
08
09
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
#!/bin/bash
  
function import_histogram(){
    echo "Importing Histogram file: $2 to $1 table"
    psql metrics postgres <<SQL
        CREATE TABLE IF NOT EXISTS $1 (
            t BIGINT,
            count BIGINT,
            max NUMERIC(19, 6),
            mean NUMERIC(19, 6),
            min NUMERIC(19, 6),
            stddev NUMERIC(19, 6),
            p50 NUMERIC(19, 6),
            p75 NUMERIC(19, 6),
            p95 NUMERIC(19, 6),
            p98 NUMERIC(19, 6),
            p99 NUMERIC(19, 6),
            p999 NUMERIC(19, 6),
            PRIMARY KEY (t)
        );
        COPY $1(
            t,
            count,
            max,
            mean,
            min,
            stddev,
            p50,
            p75,
            p95,
            p98,
            p99,
            p999
        )
        FROM '$2' WITH DELIMITER ',' CSV HEADER;
SQL
}
  
function import_timer(){
    echo "Importing Timer file: $2 to $1 table"
    psql metrics postgres <<SQL
    CREATE TABLE IF NOT EXISTS $1 (
            t BIGINT,
            count BIGINT,
            max NUMERIC(19, 6),
            mean NUMERIC(19, 6),
            min NUMERIC(19, 6),
            stddev NUMERIC(19, 6),
            p50 NUMERIC(19, 6),
            p75 NUMERIC(19, 6),
            p95 NUMERIC(19, 6),
            p98 NUMERIC(19, 6),
            p99 NUMERIC(19, 6),
            p999 NUMERIC(19, 6),
            mean_rate NUMERIC(19, 6),
            m1_rate NUMERIC(19, 6),
            m5_rate NUMERIC(19, 6),
            m15_rate NUMERIC(19, 6),
            rate_unit VARCHAR(64),
            duration_unit VARCHAR(64),
            PRIMARY KEY (t)
        );
        COPY $1(
            t,
            count,
            max,
            mean,
            min,
            stddev,
            p50,
            p75,
            p95,
            p98,
            p99,
            p999,
            mean_rate,
            m1_rate,
            m5_rate,
            m15_rate,
            rate_unit,
            duration_unit
        )
        FROM '$2' WITH DELIMITER ',' CSV HEADER;       
SQL
}
  
for csv_file in *.csv
do
    table_name=`echo ${csv_file%%.*}|sed -e 's/\([A-Z]\)/_\L\1/g'`    
    csv_folder_path="$(cygpath -w `pwd`)"
    csv_file_path=$csv_folder_path/$csv_file   
    if [[ $table_name == *histogram ]]
    then
        import_histogram $table_name $csv_file_path   
    elif [[ $table_name == *millis ]]
    then
        import_timer $table_name $csv_file_path       
    fi
done

Поскольку PostgreSQL требует пути Windows, нам нужно использовать команду $ (cygpath -w `pwd`), чтобы преобразовать пути, аналогичные Cygwin, в их эквивалент Windows.

Давайте запустим этот скрипт сейчас:

01
02
03
04
05
06
07
08
09
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
vlad@HOME /cygdrive/d/metrics
$ dos2unix codahale_metrics_csv_to_postgres.sh
dos2unix: converting file codahale_metrics_csv_to_postgres.sh to Unix format ...
 
vlad@HOME /cygdrive/d/metrics
$ ./codahale_metrics_csv_to_postgres.sh
Importing Histogram file: D:\metrics/concurrentConnectionRequestsHistogram.csv to concurrent_connection_requests_histogram table
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "concurrent_connection_requests_histogram_pkey" for table "concurrent_connection_requests_histogram"
CREATE TABLE
COPY 1537
Importing Histogram file: D:\metrics/concurrentConnectionsHistogram.csv to concurrent_connections_histogram table
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "concurrent_connections_histogram_pkey" for table "concurrent_connections_histogram"
CREATE TABLE
COPY 1537
Importing Timer file: D:\metrics/connectionAcquireMillis.csv to connection_acquire_millis table
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "connection_acquire_millis_pkey" for table "connection_acquire_millis"
CREATE TABLE
COPY 1537
Importing Timer file: D:\metrics/connectionLeaseMillis.csv to connection_lease_millis table
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "connection_lease_millis_pkey" for table "connection_lease_millis"
CREATE TABLE
COPY 1537
Importing Histogram file: D:\metrics/maxPoolSizeHistogram.csv to max_pool_size_histogram table
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "max_pool_size_histogram_pkey" for table "max_pool_size_histogram"
CREATE TABLE
COPY 1537
Importing Timer file: D:\metrics/overallConnectionAcquireMillis.csv to overall_connection_acquire_millis table
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "overall_connection_acquire_millis_pkey" for table "overall_connection_acquire_millis"
CREATE TABLE
COPY 1537
Importing Histogram file: D:\metrics/overflowPoolSizeHistogram.csv to overflow_pool_size_histogram table
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "overflow_pool_size_histogram_pkey" for table "overflow_pool_size_histogram"
CREATE TABLE
COPY 1537
Importing Histogram file: D:\metrics/retryAttemptsHistogram.csv to retry_attempts_histogram table
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "retry_attempts_histogram_pkey" for table "retry_attempts_histogram"
CREATE TABLE
COPY 1537

После запуска этого скрипта мы получили следующие полностью загруженные таблицы PostgreSQL:

codahale_csv_postgres_import

В моем следующем посте будут работать оконные функции, поскольку я хочу сравнить эмпирические метрики FLexyPool с вероятностями теории массового обслуживания.

Ссылка: Как импортировать данные CSV в PostgreSQL от нашего партнера по JCG Влада Михалча в блоге Влада Михалча .
51
Share: