Импорт данных в базы данных

Импорт данных в базы данных

Содержание:

Режимы импорта

Предположим, у нас есть файл data.csv следующего содержания:

id;"name"
1;"Customer C1"
2;"Customer C2"
3;"Customer C3"

Нам нужно импортировать данные в таблицу со следующей структурой:

CREATE TABLE [data] (
    [id] integer PRIMARY KEY NOT NULL,
    [name] varchar(50) NULL
);

Первый способ импорта данных включает две команды: make и exec:

gsqlcmd make db data.csv merge.sql /table=data /merge

gsqlcmd exec db merge.sql

Команда make генерирует следующие команды SQL в файле merge.sql:

UPDATE [data] SET [name] = 'Customer C1' WHERE [id] = 1;
UPDATE [data] SET [name] = 'Customer C2' WHERE [id] = 2;
UPDATE [data] SET [name] = 'Customer C3' WHERE [id] = 3;
INSERT INTO [data] ([name]) SELECT s.[name] FROM ( SELECT s.[id], s.[name] FROM (SELECT 1 AS [id], 'Customer C1' AS [name]) s LEFT OUTER JOIN [data] t ON t.[id] = s.[id] WHERE t.[id] IS NULL) s;
INSERT INTO [data] ([name]) SELECT s.[name] FROM ( SELECT s.[id], s.[name] FROM (SELECT 2 AS [id], 'Customer C2' AS [name]) s LEFT OUTER JOIN [data] t ON t.[id] = s.[id] WHERE t.[id] IS NULL) s;
INSERT INTO [data] ([name]) SELECT s.[name] FROM ( SELECT s.[id], s.[name] FROM (SELECT 3 AS [id], 'Customer C3' AS [name]) s LEFT OUTER JOIN [data] t ON t.[id] = s.[id] WHERE t.[id] IS NULL) s;
-- print Processed 3 merge records

Команда exec выполняет сгенерированные команды SQL.

Второй способ - использовать одну команду import:

gsqlcmd import db data.csv /table=data /merge

Команда import имеет те же параметры, что и команда make, за исключением выходного файла для команд SQL.

Вставка, обновление, объединение и удаление

Команды make и import имеют опцию commands со следующими значениями: insert, update, merge, delete.

Вы можете протестировать сгенерированные команды SQL, используя пакетный файл, как например:

gsqlcmd make db data.csv test-merge.sql  /table=data /merge
gsqlcmd make db data.csv test-insert.sql /table=data /insert
gsqlcmd make db data.csv test-update.sql /table=data /update
gsqlcmd make db data.csv test-delete.sql /table=data /delete

Результат режима merge показан выше. Ниже приведены результаты других режимов:

INSERT INTO [data] ([name]) VALUES ('Customer C1');
INSERT INTO [data] ([name]) VALUES ('Customer C2');
INSERT INTO [data] ([name]) VALUES ('Customer C3');
-- print Processed 3 insert records

UPDATE [data] SET [name] = 'Customer C1' WHERE [id] = 1;
UPDATE [data] SET [name] = 'Customer C2' WHERE [id] = 2;
UPDATE [data] SET [name] = 'Customer C3' WHERE [id] = 3;
-- print Processed 3 update records

DELETE FROM [data] WHERE [id] = 1;
DELETE FROM [data] WHERE [id] = 2;
DELETE FROM [data] WHERE [id] = 3;
-- print Processed 3 delete records

Вставка значений IDENTITY в таблицы SQL Server

Чтобы импортировать значения столбца идентификаторов в базы данных SQL Server, используйте опцию /insertIdentity.

Например:

gsqlcmd make db1 data.csv test-insert.sql /table=s61.table12 /insert /insertIdentity

Команда создает команды SQL, включая SET IDENTITY_INSERT:

SET IDENTITY_INSERT [s61].[table12] ON;
INSERT INTO [s61].[table12] ([id], [name]) VALUES (1, N'Customer C1');
INSERT INTO [s61].[table12] ([id], [name]) VALUES (2, N'Customer C2');
INSERT INTO [s61].[table12] ([id], [name]) VALUES (3, N'Customer C3');
SET IDENTITY_INSERT [s61].[table12] OFF;
GO
print 'Processed 3 insert records';
GO

Настройка наборов столбцов и имен столбцов

Первый пример выше импортирует данные со значениями столбца id.

Команда использует значения столбцов id и name, поскольку имена столбцов в исходном файле и целевой таблице совпадают.

Предположим, у нас есть другой файл data2.csv со следующим содержимым:

company_id;"company_name"
1;"Customer C1"
2;"Customer C2"
3;"Customer C3"

На первом этапе создайте команду select для исходного файла:

gsqlcmd make-select data2.csv select-data2.sql

Его содержание ниже:

SELECT
    t.company_id
    , t.company_name
FROM
    [data2.csv] t

Измените его содержимое на следующее:

SELECT
    t.company_id AS id
    , t.company_name AS name
FROM
    [data2.csv] t

И используйте команду:

gsqlcmd import db select-data2.sql /table=data /merge

Эта команда использует входной файл с расширением .sql, и gsqlcmd использует запрос из файла.

Обратите внимание, что в этом случае важно расширение .sql. В противном случае gsqlcmd будет использовать файл в качестве источника данных.

gsqlcmd имеет встроенный анализатор SELECT, который поддерживает предложения SELECT, FROM, WHERE и ORDER BY.

Таким образом, вы можете изменить наборы столбцов и имена столбцов.

Однако gsqlcmd не поддерживает JOIN, GROUP BY и другие возможности SQL.

Если вам необходимо изменить исходный файл, то вы можете сначала использовать текстовые драйверы ODBC или OLE DB для получения требуемого вывода.

Например, вы можете создать следующий запрос в файле select-data3.sql:

SELECT
    t.company_id AS id
    , t.company_name AS name
FROM
    [data2.csv] t
WHERE
    t.company_name LIKE 'Customer %'
ORDER BY
    t.company_name

Затем используйте режим exec для преобразования данных:

gsqlcmd exec data2.csv select-data3.sql data3.csv

Эта команда использует data2.csv в качестве параметра подключения. В этом случае, gsqlcmd создает строку подключения ODBC для текстового файла и выполняет select-data3.sql.

В редакции gsqlcmd Enterprise можно использовать запросы со вторым соединением.

К примеру, можно использовать единственную команду импорта следующим образом:

gsqlcmd import db data2.csv::select-data3.sql /table=data /merge

Настройка условий JOIN

Приведенные выше примеры импортируют данные, используя значения столбца id в качестве первичного ключа.

Если вместо этого вам нужно использовать другое поле, используйте опцию /keys.

Например, мы можем опустить поле id и использовать поле name в команде слияния:

gsqlcmd make db "SELECT name FROM data.csv" /table=data /merge /keys=name

Команда выведет следующие строки:

INSERT INTO [data] ([name]) SELECT s.[name] FROM (SELECT 'Customer C1' AS [name]) s LEFT OUTER JOIN [data] t ON t.[name] = s.[name] WHERE t.[name] IS NULL;
INSERT INTO [data] ([name]) SELECT s.[name] FROM (SELECT 'Customer C2' AS [name]) s LEFT OUTER JOIN [data] t ON t.[name] = s.[name] WHERE t.[name] IS NULL;
INSERT INTO [data] ([name]) SELECT s.[name] FROM (SELECT 'Customer C3' AS [name]) s LEFT OUTER JOIN [data] t ON t.[name] = s.[name] WHERE t.[name] IS NULL;
-- print Processed 3 merge records

Импорт данных из Интернет

Импорт данных из Интернет аналогичен описанному выше.

Однако для этого могут потребоваться определенные параметры веб-запроса, такие как referrer, и специальные параметры парсера для извлечения данных.

Ниже приведен пример пакетного файла, который импортирует котировки акций из Yahoo Finance:

set url="https://query1.finance.yahoo.com/v7/finance/quote?symbols={symbol}"

gsqlcmd import db %url% /table=data /merge /taskfile=symbols.txt /echoURL

Yahoo Finance возвращает результат JSON, а gsqlcmd импортирует его в таблицу "data".

В таблице те же имена столбцов, что и в исходном JSON. Т.е., команда достаточно проста.

Ниже приведен пример пакетного файла, который импортирует исторические цены акций из MSN Money:

gsqlcmd import db select-from-url.sql /table=data /merge /taskfile=symbols.txt /echoURL ^
        /referrer=https://www.msn.com/en-us/money/ ^
        /rootPath=Chart.Series /skippedNodes=IsStitched

Он использует опцию веб-запроса /referrer и параметры синтаксического анализатора /rootPath и /skippedNodes.

Кроме того, он использует запрос SELECT для переименования имен исходных столбцов JSON и добавления колонки тикера:

SELECT
    '{symbol}' AS [symbol]
    , T AS [date]
    , Op AS [open]
    , Hp AS [high]
    , Lp AS [low]
    , P AS [close]
    , V AS volume
FROM
    https://finance-services.msn.com/Market.svc/ChartAndQuotes?symbols={symbol}&chartType=1y&isEOD=True&isCS=true&isVol=true

Вы можете найти множество примеров в папке "Examples\Downloading and importing into DB" пакета загрузки gsqlcmd.

Этот веб-сайт использует куки. Продолжая использовать веб-сайт, Вы принимаете условия Политики защиты персональных данных.