OPENROWSET EM MASSA (Transact-SQL)

Sintaxe

Para SQL Server, Banco de Dados SQL do Azure, SQL Database in Fabric e Instância Gerenciada de SQL do Azure:

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ] |

   ROWS_PER_BATCH = rows_per_batch

Sintaxe do Fabric Data Warehouse

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |
   ESCAPECHAR = 'escape_char' |
   HEADER_ROW = [true|false] |
   PARSER_VERSION = 'parser_version' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ROWS_PER_BATCH = rows_per_batch

• <drive letter>:\<file path> para acessar arquivos em disco local
• \\<network-share\<file path> para acessar arquivos em compartilhamentos de rede
• adls://<container>@<storage>.dfs.core.windows.net/<file path> para acessar o Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> para acessar o Armazenamento de Blobs do Azure
• s3://<ip-address>:<port>/<file path> para acessar o armazenamento compatível com s3

A partir do SQL Server 2017 (14.x), o data_file pode estar no Armazenamento de Blobs do Azure. Para obter exemplos, consulte Exemplos de acesso em massa a dados no Armazenamento de Blobs do Azure.

• https://<storage>.blob.core.windows.net/<container>/<file path> para acessar o Armazenamento de Blobs do Azure ou o Azure Data Lake Storage
• https://<storage>.dfs.core.windows.net/<container>/<file path> para acessar o Azure Data Lake Storage
• abfss://<container>@<storage>.dfs.core.windows.net/<file path> para acessar o Azure Data Lake Storage
• https://onelake.dfs.fabric.microsoft.com/<workspaceId>/<lakehouseId>/Files/<file path> - para acessar o OneLake no Microsoft Fabric

Ao acessar dados armazenados no Azure Data Lake Storage Gen2, use os abfss://<container>@<storage>.dfs.core.windows.net/<file path> formatos URI de or https://<storage>.dfs.core.windows.net/<container>/<file path> em vez do endpoint do blob. Ambos oferecem suporte completo para o namespace hierárquico (HNS), que permite semântica de diretórios, operações otimizadas de arquivos e listas de controle de acesso (ACLs) no estilo POSIX.

Em contraste, o blob endpoint não expõe características hierárquicas do namespace e trata todos os caminhos como chaves de objeto planas. Isso pode levar a desempenho reduzido, comportamento limitado de diretórios e incompatibilidade com motores que esperam a semântica do sistema de arquivos Azure Data Lake Storage Gen2.

Você pode ler OPENROWSET(BULK) dados diretamente de arquivos armazenados no OneLake no Microsoft Fabric, especificamente da pasta Arquivos de um Fabric Lakehouse. Essa capacidade elimina a necessidade de contas externas de staging (como ADLS Gen2 ou Armazenamento de Blobs) e permite a ingestão nativa SaaS governada pelo workspace usando permissões do Fabric. Essa funcionalidade dá suporte a:

• Lendo de Files pastas em Lakehouses
• Cargas de workspace para warehouse dentro do mesmo locatário
• ID de identidade nativa usando a ID do Microsoft Entra

Veja as limitações que se aplicam tanto a quanto OPENROWSET(BULK)a COPY INTO .

Você não precisa especificar a FORMAT opção se a extensão do arquivo no caminho terminar com .csv, .tsv, .parquet, .parq, .jsonl, .ldjson, , ou .ndjson. Por exemplo, a OPENROWSET(BULK) função sabe que o formato é parquet com base na extensão no exemplo a seguir:

SELECT *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
);

Se o caminho do arquivo não terminar com uma dessas extensões, você precisará especificar uma FORMAT, por exemplo:

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'abfss://nyctlc@azureopendatastorage.blob.core.windows.net/yellow/**',
      FORMAT='PARQUET'
)

FORMATFILE

Especifica o caminho completo de um arquivo de formato. SQL Server dá suporte a dois tipos de arquivos de formato: XML e não XML.

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.xml'
)

Você precisa de um arquivo de formato para definir tipos de colunas no conjunto de resultados. A única exceção é quando você especifica SINGLE_CLOB, SINGLE_BLOB, ou SINGLE_NCLOB; nesse caso, você não precisa de um arquivo de formatação.

Para mais informações sobre arquivos de formatação, veja Usar um arquivo de formatação para importar dados em massa (SQL Server).

A partir do SQL Server 2017 (14.x), eles format_file_path podem estar no Armazenamento de Blobs do Azure. Para obter exemplos, consulte Exemplos de acesso em massa a dados no Armazenamento de Blobs do Azure.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE define o local raiz do caminho do arquivo de formato. Ao usar essa fonte de dados, você pode usar caminhos relativos na FORMATFILE opção.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

Crie a fonte de dados do arquivo de formato com CREATE EXTERNAL DATA SOURCE. Além da localização raiz, ele pode definir uma credencial personalizada para acessar os arquivos naquela localização.

VERSÃO_DO_ANALISADOR

Aplica-se a: Apenas Fabric Data Warehouse

Especifica a versão do analisador a ser usada ao ler arquivos. As versões do analisador com CSV suporte no momento são 1.0 e 2.0:

• PARSER_VERSION = '1,0'
• PARSER_VERSION = '2.0'

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'abfss://nyctlc@azureopendatastorage.blob.core.windows.net/yellow/**',
      FORMAT='CSV',
      PARSER_VERSION = '2.0'
)

A versão 2.0 do parser CSV é a implementação padrão otimizada para desempenho, mas não suporta todas as opções e codificações legadas disponíveis na versão 1.0. Ao usar o OPENROWSET, o Fabric Data Warehouse automaticamente volta para a versão 1.0 se você usar as opções suportadas apenas nessa versão, mesmo quando a versão não está explicitamente especificada. Em alguns casos, pode ser necessário especificar explicitamente a versão 1.0 para resolver erros causados por recursos não suportados relatados pela versão 2.0 do analisador.

Especificações do analisador CSV versão 1.0:

• As opções a seguir não têm suporte: HEADER_ROW.
• Terminadores padrão são \r\n, \n e \r.
• Se você especificar \n (newline) como o terminador de linha, ele será automaticamente prefixado com um \r caractere (retorno de carro), o que resultará em um terminador de linha de \r\n.

Especificações do analisador CSV versão 2.0:

• não há suporte para todos os tipos de dados.
• O tamanho máximo da coluna de caracteres é 8.000.
• O limite de tamanho máximo de linha é de 8 MB.
• Não há suporte para as seguintes opções: DATA_COMPRESSION.
• A cadeia de caracteres vazia entre aspas ("") é interpretada como cadeia de caracteres vazia.
• DATEFORMAT SET A opção não é aceite.
• Formato com suporte para o tipo de dados de data : YYYY-MM-DD
• Formato com suporte para o tipo de dados de tempo : HH:MM:SS[.fractional seconds]
• Formato com suporte para o tipo de dados datetime2 : YYYY-MM-DD HH:MM:SS[.fractional seconds]
• Terminadores padrão são \r\n e \n.

ESCAPE_CHAR

Especifica o caractere no arquivo que é usado para escapar de si mesmo e todos os valores delimitadores no arquivo, por exemplo:

Place,Address,Icon
Empire State Building,20 W 34th St\, New York\, NY 10118,\\icons\\sol.png
Statue of Liberty,Liberty Island\, New York\, NY 10004,\\icons\\sol.png

Se o caractere de escape for seguido por um valor diferente dele mesmo ou por um dos valores delimitadores, o caractere de escape será removido durante a leitura do valor.

O ESCAPECHAR parâmetro é aplicado independentemente de o FIELDQUOTE parâmetro estar ou não habilitado. Ele não será usado para fazer escape do caractere de aspas. O caractere de aspas deve ter escape com outro caractere de aspas. O caractere de aspas só poderá aparecer dentro do valor da coluna se o valor for encapsulado com aspas de caracteres.

No exemplo a seguir, vírgula (,) e barra invertida (\) são escapadas e representadas como \, e \\:

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ESCAPECHAR = '\'
);

HEADER_ROW

Especifica se um arquivo CSV contém uma linha de cabeçalho que não deve ser retornada com outras linhas de dados. Um exemplo de arquivo CSV com um cabeçalho é mostrado no exemplo a seguir:

Place,Latitude,Longitude,Address,Area,State,Zipcode
Empire State Building,40.748817,-73.985428,20 W 34th St,New York,NY,10118
Statue of Liberty,40.689247,-74.044502,Liberty Island,New York,NY,10004

O padrão é FALSE. Suportado no PARSER_VERSION='2.0' Fabric Data Warehouse. Se TRUE, os nomes das colunas serão lidos da primeira linha de acordo com o FIRSTROW argumento. Se TRUE e o esquema for especificado usando WITH, a associação de nomes de coluna será feita pelo nome da coluna, não por posições ordinais.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    HEADER_ROW = TRUE
);

ORDER ( { coluna [ ASC | DESC ] } [ ,... n ] [ ÚNICO ] )

Uma dica opcional que especifica como os dados são classificados no arquivo de dados. Por padrão, a operação em massa presume que o arquivo de dados não está ordenado. O desempenho pode melhorar se o otimizador de consulta puder explorar a ordem para gerar um plano de consulta mais eficiente. A lista a seguir fornece exemplos de quando a especificação de uma classificação pode ser benéfica:

• Ao inserir linhas em uma tabela que tem um índice clusterizado, na qual os dados dos conjuntos de linhas são classificados na chave do índice clusterizado.
• Ao unir o conjunto de linhas com outra tabela, cujas colunas de classificação e de união correspondam.
• Ao agregar os dados dos conjuntos de linhas pelas colunas de classificação.
• Usando o conjunto de linhas como uma tabela de origem na FROM cláusula de uma consulta, onde as colunas de classificação e junção correspondem.

UNIQUE

Especifica que o arquivo de dados não tem entradas duplicadas.

Se as linhas reais no arquivo de dados não estiverem ordenadas de acordo com a ordem que você especificou, ou se você especificar a UNIQUE dica e as chaves duplicadas estiverem presentes, um erro é retornado.

Aliases de coluna são necessários quando você usa ORDER. A lista de alias da coluna deve referenciar a tabela derivada acessada pela BULK cláusula. Os nomes das colunas que você especifica na ORDER cláusula referem-se a esta lista de alias. Você não pode especificar colunas de tipos de valor grande (varchar(max), nvarchar(max), varbinary(max) e xml) e tipos de objetos grandes (LOB) (texto, ntext e image).

Opções de conteúdo

SINGLE_BLOB

Retorna o conteúdo de data_file como um conjunto de linhas de uma única coluna do tipo varbinary(max).

SINGLE_CLOB

Lê data_file como ASCII e retorna o conteúdo como um conjunto de linhas únicas e coluna única do tipo varchar(max), usando a colação do banco de dados atual.

SINGLE_NCLOB

Lê data_file como Unicode e retorna o conteúdo como um conjunto de linhas de uma única linha e coluna única do tipo nvarchar(max), usando a colação do banco de dados atual.

SELECT * FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_NCLOB
) AS Document;

COM esquema

O esquema WITH especifica as colunas que definem o conjunto de resultados da função OPENROWSET. Inclui definições de colunas para cada coluna que OPENROWSET retorna e delineia as regras de mapeamento que vinculam as colunas do arquivo subjacente às colunas do conjunto de resultados.

No exemplo a seguir:

• A country_region coluna tem o tipo varchar(50) e faz referência à coluna subjacente com o mesmo nome.
• A date coluna faz referência a uma coluna CSV, Parquet ou propriedade JSONL com um nome físico diferente.
• A cases coluna faz referência à terceira coluna do arquivo.
• A fatal_cases coluna faz referência a uma propriedade Parquet aninhada ou subobjeto JSONL.

SELECT *
FROM OPENROWSET(<...>) 
WITH (
        country_region varchar(50), --> country_region column has varchar(50) type and referencing the underlying column with the same name
        [date] DATE '$.updated',   --> date is referencing a CSV/Parquet column or JSONL property with a different physical name
        cases INT 3,             --> cases is referencing third column in the file
        fatal_cases INT '$.statistics.deaths'  --> fatal_cases is referencing a nested Parquet property or JSONL sub-object
     );

<Column_name>

O nome da coluna que OPENROWSET retorna no conjunto de linhas do resultado. OPENROWSET lê dados dessa coluna da coluna de arquivo subjacente com o mesmo nome, a menos que você a sobrescrita usando <column_path> ou <column_ordinal>. O nome da coluna deve seguir as regras para identificadores de nome de coluna.

<Column_type>

O tipo T-SQL da coluna no conjunto de resultados. OPENROWSET converte valores do arquivo subjacente para esse tipo quando retorna os resultados. Para obter mais informações, consulte Os tipos de dados no Fabric Warehouse.

<column_path>

Um caminho separado por ponto (por exemplo, $.description.location.lat) usado para referenciar campos aninhados em tipos complexos como Parquet.

<column_ordinal>

Um número que representa o índice físico da coluna que corresponde à coluna da WITH cláusula.

• Ao usar OPENROWSET(BULK...) como tabela de origem em uma INSERT instrução ou, MERGE você importa dados em massa de um arquivo de dados para uma tabela. Para obter mais informações, consulte Use BULK INSERT ou OPENROWSET(BULK...) para importar dados para SQL Server.
• Quando você usa a OPENROWSET BULK opção com uma INSERT instrução, a BULK cláusula suporta dicas de tabela. Além de dicas de tabela normais, como TABLOCK, a cláusula BULK pode aceitar as seguintes dicas de tabela especializadas: IGNORE_CONSTRAINTS (ignora somente as restrições CHECK e FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS e KEEPIDENTITY. Para obter mais informações, confira Dicas de tabela (Transact-SQL).
• Para obter informações sobre como usar instruções INSERT...SELECT * FROM OPENROWSET(BULK...), confira Importação e exportação em massa de dados (SQL Server). Para informações sobre quando as operações de inserção de linhas realizadas pela importação em massa são registradas no registro de transações, veja Pré-requisitos para um registro mínimo na importação em massa.
• Quando você usa OPENROWSET (BULK ...) para importar dados com o modelo de recuperação completo, não otimiza o registro.

Em Microsoft Fabric Data Warehouse, a tabela a seguir resume as funcionalidades suportadas:

Importação em massa de dados SQLCHAR, SQLNCHAR ou SQLBINARY

OPENROWSET(BULK...) assume que, se você não especificar o contrário, o comprimento máximo de SQLCHAR, SQLNCHAR, ou SQLBINARY dados não ultrapassa 8.000 bytes. Se você estiver importando dados em um campo de dados LOB que contenha quaisquer objetos varchar(max), nvarchar(max) ou varbinary(max) que excedam 8.000 bytes, você deve usar um arquivo em formato XML que defina o comprimento máximo do campo de dados. Para especificar o comprimento máximo, edite o arquivo de formato e declare o MAX_LENGTH atributo.

Exportar ou importar documentos SQLXML em massa

Para exportar ou importar dados SQLXML em massa, use um dos tipos de dados a seguir em seu arquivo de formato.

A. Use OPENROWSET para BULK INSERT arquivar dados em uma coluna varbinary(max)

Aplica-se a: Somente o SQL Server.

O exemplo a Text1.txt seguir cria uma tabela pequena para fins de demonstração e insere dados de arquivo de um arquivo nomeado C: localizado no diretório raiz em uma coluna varbinary(max).

CREATE TABLE myTable (
    FileName NVARCHAR(60),
    FileType NVARCHAR(60),
    Document VARBINARY(MAX)
);
GO

INSERT INTO myTable (
    FileName,
    FileType,
    Document
)
SELECT 'Text1.txt' AS FileName,
    '.txt' AS FileType,
    *
FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_BLOB
) AS Document;
GO

B. Usar o provedor OPENROWSET BULK com um arquivo de formato para recuperar linhas de um arquivo de texto

Aplica-se a: Somente o SQL Server.

O exemplo a seguir usa um arquivo de formato para recuperar linhas de um arquivo de texto delimitado por tabulação, values.txt, que contém os seguintes dados:

1     Data Item 1
2     Data Item 2
3     Data Item 3

O arquivo de formato values.fmt descreve as colunas em values.txt:

9.0
2
1  SQLCHAR  0  10 "\t"    1  ID           SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"  2  Description  SQL_Latin1_General_Cp437_BIN

Esta consulta recupera esses dados:

SELECT a.* FROM OPENROWSET(
    BULK 'C:\test\values.txt',
   FORMATFILE = 'C:\test\values.fmt'
) AS a;

C. Especificar um arquivo de formato e uma página de código

Aplica-se a: Somente o SQL Server.

O exemplo a seguir mostra como usar as opções de arquivo de formato e página de código ao mesmo tempo.

INSERT INTO MyTable
SELECT a.* FROM OPENROWSET (
    BULK N'D:\data.csv',
    FORMATFILE = 'D:\format_no_collation.txt',
    CODEPAGE = '65001'
) AS a;

D. Acessar dados de um arquivo CSV com um arquivo de formato

Aplica-se a: Somente o SQL Server 2017 (14.x) e versões posteriores.

SELECT * FROM OPENROWSET(
    BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW = 2,
    FORMAT = 'CSV'
) AS cars;

E. Acessar dados de um arquivo CSV sem um arquivo de formato

Aplica-se a: Somente o SQL Server.

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

SELECT *
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
    'SELECT * FROM E:\Tlog\TerritoryData.csv'
);

F. Acessar dados de um arquivo armazenado no Armazenamento de Blobs do Azure

Aplica-se a: Somente o SQL Server 2017 (14.x) e versões posteriores.

No SQL Server 2017 (14.x) e versões posteriores, o exemplo a seguir usa uma fonte de dados externa que aponta para um contêiner em uma conta de armazenamento do Azure e uma credencial no escopo do banco de dados criada para uma assinatura de acesso compartilhado.

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   SINGLE_CLOB
) AS DataFile;

Para obter exemplos completos OPENROWSET , incluindo a configuração da credencial e da fonte de dados externa, consulte Exemplos de acesso em massa a dados no Armazenamento de Blobs do Azure.

G. Importar para uma tabela de um arquivo armazenado no Armazenamento de Blobs do Azure

O exemplo a seguir mostra como usar o OPENROWSET comando para carregar dados de um arquivo CSV em um local de armazenamento Azure Blob onde você criou a chave SAS. Você configura o local de armazenamento do Azure Blob como uma fonte de dados externa. Esse processo requer uma credencial com escopo de banco de dados que utilize uma assinatura de acesso compartilhado (SAS) criptografada por meio de uma chave mestra no banco de dados do usuário.

-- Optional: a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

-- Optional: a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- Make sure that you don't have a leading ? in the SAS token, and that you
-- have at least read permission on the object that should be loaded srt=o&sp=r,
-- and that expiration period is valid (all dates are in UTC time)
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/curriculum',
    -- CREDENTIAL is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

INSERT INTO achievements
WITH (TABLOCK) (
    id,
    description
)
SELECT * FROM OPENROWSET(
    BULK 'csv/achievements.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FORMATFILE = 'csv/achievements-c.xml',
    FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;

H. Usar uma identidade gerenciada para uma fonte externa

Aplica-se a: Instância Gerenciada de SQL do Azure e Banco de Dados SQL do Azure

O exemplo a seguir cria uma credencial usando uma identidade gerenciada, cria uma fonte externa e então carrega dados de um CSV hospedado na fonte externa.

Primeiro, crie a credencial e especifique o armazenamento de blobs como a fonte externa:

CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    LOCATION = 'abs://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

Em seguida, carregue dados do arquivo CSV hospedado no armazenamento de blobs:

SELECT * FROM OPENROWSET(
    BULK 'Test - Copy.csv',
    DATA_SOURCE = 'SampleSource',
    SINGLE_CLOB
) as test;

I. Use OPENROWSET para acessar vários arquivos Parquet usando o armazenamento de objetos compatível com S3

Aplica-se a : SQL Server 2022 (16.x) e versões posteriores.

O exemplo a seguir acessa vários arquivos Parquet de diferentes locais, todos armazenados em armazenamento de objetos compatíveis com S3:

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

J. Usar OPENROWSET para acessar várias tabelas Delta do Azure Data Lake Gen2

Aplica-se a : SQL Server 2022 (16.x) e versões posteriores.

Neste exemplo, o contêiner da tabela de dados é chamado Contoso e está localizado em uma conta de armazenamento do Azure Data Lake Gen2.

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

K. Usar OPENROWSET para consultar o conjunto de dados público-anônimo

O exemplo a seguir usa o conjunto de dados aberto de registros de corrida de táxi amarelo de NYC disponíveis publicamente.

Crie a fonte de dados primeiro:

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Consultar todos os arquivos com .parquet extensão em pastas que correspondem ao padrão de nome:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

A. Ler um arquivo parquet do Armazenamento de Blobs do Azure

No exemplo a seguir, você pode ver como ler 100 linhas de um arquivo Parquet:

SELECT TOP 100 * 
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
);

B. Ler um arquivo CSV personalizado

No exemplo a seguir, você pode ver como ler linhas de um arquivo CSV com uma linha de cabeçalho e caracteres exterminadores explicitamente especificados que separam linhas e campos:

SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv',
 HEADER_ROW = TRUE,
 ROW_TERMINATOR = '\n',
 FIELD_TERMINATOR = ',');

C. Especificar o esquema de coluna de arquivo durante a leitura de um arquivo

No exemplo a seguir, você especifica o esquema da linha que a OPENROWSET função retorna:

SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') 
WITH (
        updated DATE
        ,confirmed INT
        ,deaths INT
        ,iso2 VARCHAR(8000)
        ,iso3 VARCHAR(8000)
        );

D. Ler conjuntos de dados particionados

No exemplo a seguir, você usa a filepath() função para ler as partes do URI a partir do caminho do arquivo correspondente:

SELECT TOP 10 
  files.filepath(2) AS area
, files.*
FROM OPENROWSET(
BULK 'https://<storage account>.blob.core.windows.net/public/NYC_Property_Sales_Dataset/*_*.csv',
 HEADER_ROW = TRUE) 
AS files
WHERE files.filepath(1) = '2009';

E. Especificar o esquema de coluna de arquivo ao ler um arquivo JSONL

No exemplo a seguir, você pode ver como especificar explicitamente o esquema de linha que será retornado como resultado da OPENROWSET função:

SELECT TOP 10 *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.dfs.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl') 
WITH (
        country_region varchar(50),
        date DATE '$.updated',
        cases INT '$.confirmed',
        fatal_cases INT '$.deaths'
     );

Se um nome de coluna não corresponder ao nome físico de uma coluna nas propriedades se o arquivo JSONL for especificado, você poderá especificar o nome físico no caminho JSON após a definição de tipo. Você pode usar várias propriedades. Por exemplo, $.location.latitude para fazer referência às propriedades aninhadas em tipos complexos parquet ou sub-objetos JSON.

Mais exemplos

• Procurar conteúdo do arquivo usando a função OPENROWSET
• Diretrizes de desempenho para o Fabric Data Warehouse
• Ingerir dados no Warehouse usando Transact-SQL

A. Use o OPENROWSET para ler um arquivo CSV de um Fabric Lakehouse

Neste exemplo, você lê OPENROWSET um arquivo CSV em um Fabric Lakehouse. O arquivo recebe um nome customer.csv e é armazenado na Files/Contoso/ pasta. Como você não fornece uma fonte de dados ou credenciais com escopo de banco de dados, o banco de dados Fabric SQL usa seu contexto Entra ID para autenticar.

SELECT * FROM OPENROWSET 
( BULK ' abfss://<workspace id>@<tenant>.dfs.fabric.microsoft.com/<lakehouseid>/Files/Contoso/customer.csv' 
, FORMAT = 'CSV' 
, FIRST_ROW = 2 
) WITH 
(  
    CustomerKey INT,  
    GeoAreaKey INT,  
    StartDT DATETIME2,  
    EndDT DATETIME2,  
    Continent NVARCHAR(50),  
    Gender NVARCHAR(10),  
    Title NVARCHAR(10),  
    GivenName NVARCHAR(100),  
    MiddleInitial VARCHAR(2),  
    Surname NVARCHAR(100),  
    StreetAddress NVARCHAR(200),  
    City NVARCHAR(100),  
    State NVARCHAR(100),  
    StateFull NVARCHAR(100),  
    ZipCode NVARCHAR(20),  
    Country_Region NCHAR(2),  
    CountryFull NVARCHAR(100),  
    Birthday DATETIME2,  
    Age INT,  
    Occupation NVARCHAR(100),  
    Company NVARCHAR(100),  
    Vehicle NVARCHAR(100),  
    Latitude DECIMAL(10,6),  
    Longitude DECIMAL(10,6) ) AS DATA 

B. Use o OPENROWSET para ler um arquivo de um Fabric Lakehouse e inserir dados em uma nova tabela

Neste exemplo, você utiliza OPENROWSET para ler dados de um arquivo Parquet chamado store.parquet. Depois, você adiciona INSERT os dados a uma nova tabela chamada Store. O arquivo Parquet está localizado em uma casa de lago Fabric. Como você não fornece uma fonte de dados ou credenciais com escopo de banco de dados, o banco de dados SQL no Fabric usa seu contexto do Entra ID para autenticar.

SELECT * 
FROM OPENROWSET 
(BULK 'abfss://<workspace id>@<tenant>.dfs.fabric.microsoft.com/<lakehouseid>/Files/Contoso/store.parquet' 
, FORMAT = 'parquet' )
 AS dataset; 

-- insert into new table 
SELECT * 
INTO Store 
FROM OPENROWSET 
(BULK 'abfss://<workspace id>@<tenant>.dfs.fabric.microsoft.com/<lakehouseid>/Files/Contoso/store.parquet' 
, FORMAT = 'parquet' ) 
 AS STORE; 

Mais exemplos

Para obter mais exemplos que mostram o uso OPENROWSET(BULK...)do , consulte os seguintes artigos:

• Importação e exportação em massa de dados (SQL Server)
• Exemplos de importação e exportação em massa de documentos XML (SQL Server)
• Manter valores de identidade ao importar dados em massa (SQL Server)
• Manter valores nulos ou padrão durante a importação em massa (SQL Server)
• Usar um arquivo de formato para importação em massa de dados (SQL Server)
• Usar o formato de caractere para importar ou exportar dados (SQL Server)
• Usar um arquivo de formato para ignorar uma coluna de tabela (SQL Server)
• Usar um arquivo de formato para ignorar um campo de dados (SQL Server)
• Usar um arquivo de formato para mapear colunas de tabela para campos de arquivo de dados (SQL Server)
• Consultar fontes de dados usando OPENROWSET em Instâncias Gerenciadas de SQL do Azure
• Especificar terminadores de linha e de campo (SQL Server)