Odpowiedzi:
PostgreSQL zawiera przewodnik na temat tego, jak najlepiej zapełnić bazę danych na początku, i sugeruje użycie polecenia KOPIUJ do masowego ładowania wierszy. Przewodnik zawiera kilka innych dobrych wskazówek, jak przyspieszyć proces, takich jak usuwanie indeksów i kluczy obcych przed załadowaniem danych (i dodaniem ich później).
Istnieje alternatywa dla korzystania z COPY, która jest składnią wielu wartości, którą obsługuje Postgres. Z dokumentacji :
INSERT INTO films (code, title, did, date_prod, kind) VALUES
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
Powyższy kod wstawia dwa wiersze, ale możesz go dowolnie rozszerzyć, dopóki nie osiągniesz maksymalnej liczby przygotowanych tokenów instrukcji (może to być 999 USD, ale nie jestem w 100% pewien). Czasami nie można korzystać z funkcji KOPIUJ i jest to godny zamiennik w takich sytuacjach.
Jednym ze sposobów przyspieszenia jest jawne wykonanie wielu wstawień lub kopii w ramach transakcji (powiedzmy 1000). Domyślnym zachowaniem Postgresa jest zatwierdzanie po każdej instrukcji, więc przez grupowanie zatwierdzeń można uniknąć pewnego narzutu. Jak mówi przewodnik w odpowiedzi Daniela, może być konieczne wyłączenie automatycznego zatwierdzania, aby to zadziałało. Zwróć również uwagę na komentarz na dole, który sugeruje zwiększenie wielkości buforów wal_buffers do 16 MB.
UNNEST
funkcja z tablicami może być używana wraz ze składnią multirow VALUES. Myślę, że ta metoda jest wolniejsza niż używanie, COPY
ale przydaje mi się w pracy z psycopg i python (python list
przekazany na cursor.execute
pg ARRAY
):
INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
VALUES (
UNNEST(ARRAY[1, 2, 3]),
UNNEST(ARRAY[100, 200, 300]),
UNNEST(ARRAY['a', 'b', 'c'])
);
bez VALUES
użycia podselekcji z dodatkową kontrolą istnienia:
INSERT INTO tablename (fieldname1, fieldname2, fieldname3)
SELECT * FROM (
SELECT UNNEST(ARRAY[1, 2, 3]),
UNNEST(ARRAY[100, 200, 300]),
UNNEST(ARRAY['a', 'b', 'c'])
) AS temptable
WHERE NOT EXISTS (
SELECT 1 FROM tablename tt
WHERE tt.fieldname1=temptable.fieldname1
);
ta sama składnia dla aktualizacji zbiorczych:
UPDATE tablename
SET fieldname1=temptable.data
FROM (
SELECT UNNEST(ARRAY[1,2]) AS id,
UNNEST(ARRAY['a', 'b']) AS data
) AS temptable
WHERE tablename.id=temptable.id;
Możesz użyć opcji COPY table TO ... WITH BINARY
„ nieco szybszej niż formaty tekstowe i CSV ”. Zrób to tylko wtedy, gdy masz miliony wierszy do wstawienia i nie masz problemów z danymi binarnymi.
Oto przykładowy przepis w Pythonie, wykorzystujący psycopg2 z wejściem binarnym .
Zależy to głównie od (innej) aktywności w bazie danych. Takie operacje skutecznie zamrażają całą bazę danych na inne sesje. Innym zagadnieniem jest model danych i obecność ograniczeń, wyzwalaczy itp.
Moje pierwsze podejście to zawsze: utwórz tabelę (temp) o strukturze podobnej do tabeli docelowej (utwórz tabelę tmp AS wybierz * z celu, gdzie 1 = 0) i zacznij od odczytu pliku do tabeli temp. Następnie sprawdzam, co można sprawdzić: duplikaty, klucze, które już istnieją w celu itp.
Następnie wykonuję polecenie „do insert into target select * from tmp” lub podobne.
Jeśli to się nie powiedzie lub zajmie to zbyt długo, przerywam to i rozważam inne metody (tymczasowe usuwanie indeksów / ograniczeń itp.)
Zaimplementowałem bardzo szybki moduł ładujący Postgresq z natywnymi metodami libpq. Wypróbuj mój pakiet https://www.nuget.org/packages/NpgsqlBulkCopy/
Właśnie napotkałem ten problem i polecam csvsql ( wersje ) do masowego importu do Postgres. Aby wykonać wstawianie zbiorcze, którego po prostu createdb
użyjesz csvsql
, a następnie użyjesz , aby połączyć się z bazą danych i utworzyć indywidualne tabele dla całego folderu CSV.
$ createdb test
$ csvsql --db postgresql:///test --insert examples/*.csv
Termin „dane zbiorcze” odnosi się do „dużej ilości danych”, więc naturalne jest używanie oryginalnych danych surowych , bez potrzeby przekształcania ich w SQL. Typowe pliki surowych danych dla „wstawiania zbiorczego” to CSV i JSON formaty .
W aplikacjach ETL i procesach przetwarzania musimy zmienić dane przed ich wstawieniem. Tabela tymczasowa zajmuje (dużo) miejsca na dysku i nie jest to najszybszy sposób na zrobienie tego. PostgreSQL wrapper obcych dane (FDW) jest najlepszym wyborem.
Przykład CSV . Załóżmy, że tablename (x, y, z)
plik SQL lub CSV jest podobny
fieldname1,fieldname2,fieldname3
etc,etc,etc
... million lines ...
Możesz użyć klasycznego kodu SQL, COPY
aby załadować ( tak jak oryginalne dane) tmp_tablename
, wstawić przefiltrowane dane do tablename
... Ale, aby uniknąć zużycia dysku, najlepiej jest je pobrać bezpośrednio przez
INSERT INTO tablename (x, y, z)
SELECT f1(fieldname1), f2(fieldname2), f3(fieldname3) -- the transforms
FROM tmp_tablename_fdw
-- WHERE condictions
;
Musisz przygotować bazę danych dla FDW, a zamiast statycznego tmp_tablename_fdw
możesz użyć funkcji, która ją generuje :
CREATE EXTENSION file_fdw;
CREATE SERVER import FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE tmp_tablename_fdw(
...
) SERVER import OPTIONS ( filename '/tmp/pg_io/file.csv', format 'csv');
Przykład JSON . Zestaw dwóch plików myRawData1.json
i Ranger_Policies2.json
może być spożywana przez:
INSERT INTO tablename (fname, metadata, content)
SELECT fname, meta, j -- do any data transformation here
FROM jsonb_read_files('myRawData%.json')
-- WHERE any_condiction_here
;
gdzie funkcja jsonb_read_files () odczytuje wszystkie pliki folderu zdefiniowane przez maskę:
CREATE or replace FUNCTION jsonb_read_files(
p_flike text, p_fpath text DEFAULT '/tmp/pg_io/'
) RETURNS TABLE (fid int, fname text, fmeta jsonb, j jsonb) AS $f$
WITH t AS (
SELECT (row_number() OVER ())::int id,
f as fname,
p_fpath ||'/'|| f as f
FROM pg_ls_dir(p_fpath) t(f)
WHERE f like p_flike
) SELECT id, fname,
to_jsonb( pg_stat_file(f) ) || jsonb_build_object('fpath',p_fpath),
pg_read_file(f)::jsonb
FROM t
$f$ LANGUAGE SQL IMMUTABLE;
Najczęstszą metodą „pobierania plików” (głównie w Big Data) jest zachowanie oryginalnego pliku w formacie gzip i przesłanie go za pomocą algorytmu przesyłania strumieniowego , wszystko, co może działać szybko i bez zużycia dysku w potokach unix:
gunzip remote_or_local_file.csv.gz | convert_to_sql | psql
Tak więc idealna (przyszłość) jest opcją formatu dla serwera.csv.gz
.