Импорт данных в базы данных
Содержание:
- Режимы импорта
- Вставка, обновление, объединение и удаление
- Вставка значений IDENTITY в таблицы SQL Server
- Настройка наборов столбцов и имен столбцов
- Настройка условий JOIN
- Импорт данных из Интернета
Режимы импорта
Предположим, у нас есть файл 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
.