Wykonywanie zapytań o pliki CSV

  • Artykuł

W tym artykule dowiesz się, jak wykonywać zapytania dotyczące pojedynczego pliku CSV przy użyciu bezserwerowej puli SQL w usłudze Azure Synapse Analytics. Pliki CSV mogą mieć różne formaty:

  • Z wierszem nagłówka i bez
  • Wartości rozdzielane przecinkami i tabulatorami
  • Zakończenia linii stylu systemu Windows i Unix
  • Niecytowane i cytowane wartości oraz znaki ucieczki

Wszystkie powyższe odmiany zostaną omówione poniżej.

Przykład z przewodnika Szybki start

OPENROWSET funkcja umożliwia odczytywanie zawartości pliku CSV przez podanie adresu URL do pliku.

Odczytywanie pliku CSV

Najprostszym sposobem wyświetlenia zawartości CSV pliku jest podanie adresu URL pliku do OPENROWSET działania, określenie pliku csv FORMATi 2.0 PARSER_VERSION. Jeśli plik jest publicznie dostępny lub jeśli tożsamość firmy Microsoft Entra może uzyskać dostęp do tego pliku, powinna być widoczna zawartość pliku przy użyciu zapytania, takiego jak pokazany w poniższym przykładzie:

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
    format = 'csv',
    parser_version = '2.0',
    firstrow = 2 ) as rows

Opcja firstrow służy do pomijania pierwszego wiersza w pliku CSV, który reprezentuje nagłówek w tym przypadku. Upewnij się, że masz dostęp do tego pliku. Jeśli plik jest chroniony przy użyciu klucza sygnatury dostępu współdzielonego lub tożsamości niestandardowej, musisz skonfigurować poświadczenia na poziomie serwera na potrzeby logowania sql.

Ważne

Jeśli plik CSV zawiera znaki UTF-8, upewnij się, że używasz sortowania bazy danych UTF-8 (na przykład Latin1_General_100_CI_AS_SC_UTF8). Niezgodność między kodowaniem tekstu w pliku a sortowaniem może spowodować nieoczekiwane błędy konwersji. Domyślne sortowanie bieżącej bazy danych można łatwo zmienić przy użyciu następującej instrukcji języka T-SQL: alter database current collate Latin1_General_100_CI_AI_SC_UTF8

Użycie źródła danych

Poprzedni przykład używa pełnej ścieżki do pliku. Alternatywnie możesz utworzyć zewnętrzne źródło danych z lokalizacją wskazującą folder główny magazynu:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );

Po utworzeniu źródła danych możesz użyć tego źródła danych i ścieżki względnej do pliku w OPENROWSET funkcji:

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.csv',
        data_source = 'covid',
        format = 'csv',
        parser_version ='2.0',
        firstrow = 2
    ) as rows

Jeśli źródło danych jest chronione za pomocą klucza sygnatury dostępu współdzielonego lub tożsamości niestandardowej, możesz skonfigurować źródło danych przy użyciu poświadczeń o zakresie bazy danych.

Jawne określanie schematu

OPENROWSET Umożliwia jawne określenie kolumn, które mają być odczytywane z pliku przy użyciu WITH klauzuli :

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.csv',
        data_source = 'covid',
        format = 'csv',
        parser_version ='2.0',
        firstrow = 2
    ) with (
        date_rep date 1,
        cases int 5,
        geo_id varchar(6) 8
    ) as rows

Liczby po typie danych w klauzuli WITH reprezentują indeks kolumny w pliku CSV.

Ważne

Jeśli plik CSV zawiera znaki UTF-8, upewnij się, że określono sortowanie UTF-8 (na przykład Latin1_General_100_CI_AS_SC_UTF8) dla wszystkich kolumn w WITH klauzuli lub ustaw sortowanie UTF-8 na poziomie bazy danych. Niezgodność między kodowaniem tekstu w pliku i sortowaniu może spowodować nieoczekiwane błędy konwersji. Domyślne sortowanie bieżącej bazy danych można łatwo zmienić przy użyciu następującej instrukcji języka T-SQL: alter database current collate Latin1_General_100_CI_AI_SC_UTF8 Sortowanie typów koloków można łatwo ustawić przy użyciu następującej definicji: geo_id varchar(6) collate Latin1_General_100_CI_AI_SC_UTF8 8

W poniższych sekcjach przedstawiono sposób wykonywania zapytań dotyczących różnych typów plików CSV.

Wymagania wstępne

Pierwszym krokiem jest utworzenie bazy danych , w której zostaną utworzone tabele. Następnie zainicjuj obiekty, wykonując skrypt instalacyjny w tej bazie danych. Ten skrypt instalacyjny utworzy źródła danych, poświadczenia o zakresie bazy danych i zewnętrzne formaty plików, które są używane w tych przykładach.

Nowy wiersz stylu systemu Windows

Poniższe zapytanie pokazuje, jak odczytać plik CSV bez wiersza nagłówka z nowym wierszem w stylu systemu Windows i kolumnami rozdzielanymi przecinkami.

Podgląd pliku:

First 10 rows of the CSV file without header, Windows style new line.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017;

Nowa linia w stylu unix

Poniższe zapytanie pokazuje, jak odczytać plik bez wiersza nagłówka z nowym wierszem w stylu unix i kolumnami rozdzielanymi przecinkami. Zwróć uwagę na inną lokalizację pliku w porównaniu z innymi przykładami.

Podgląd pliku:

First 10 rows of the CSV file without header row and with Unix-Style new line.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017;

Wiersz nagłówka

Poniższe zapytanie pokazuje, jak plik odczytu z wierszem nagłówka z nowym wierszem w stylu unix i kolumnami rozdzielanymi przecinkami. Zwróć uwagę na inną lokalizację pliku w porównaniu z innymi przykładami.

Podgląd pliku:

First 10 rows of the CSV file with header row and with Unix-Style new line.

SELECT *
FROM OPENROWSET(
    BULK 'csv/population-unix-hdr/population.csv',
    DATA_SOURCE = 'SqlOnDemandDemo',
    FORMAT = 'CSV', PARSER_VERSION = '2.0',
    FIELDTERMINATOR =',',
    HEADER_ROW = TRUE
    ) AS [r]

Opcja HEADER_ROW = TRUE spowoduje odczytywanie nazw kolumn z wiersza nagłówka w pliku. Jest to doskonałe rozwiązanie do celów eksploracji, gdy nie znasz zawartości pliku. Aby uzyskać najlepszą wydajność, zobacz sekcję Używanie odpowiednich typów danych w sekcji Najlepsze rozwiązania. Więcej informacji na temat składni OPENROWSET można również znaleźć tutaj.

Znak cudzysłowu niestandardowego

Poniższe zapytanie pokazuje, jak odczytać plik z wierszem nagłówka z nowym wierszem w stylu unix, kolumnami rozdzielanymi przecinkami i cytowanymi wartościami. Zwróć uwagę na inną lokalizację pliku w porównaniu z innymi przykładami.

Podgląd pliku:

First 10 rows of the CSV file with header row and with Unix-Style new line and quoted values.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-quoted/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2,
        FIELDQUOTE = '"'
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017;

Uwaga

To zapytanie zwróci te same wyniki, jeśli pominięto parametr FIELDQUOTE, ponieważ wartość domyślna fieldQUOTE jest podwójnym cudzysłowem.

Znaki ucieczki

Poniższe zapytanie pokazuje, jak odczytać plik z wierszem nagłówka z nowym wierszem w stylu systemu Unix, kolumnami rozdzielanymi przecinkami i znakiem ucieczki używanym dla ogranicznika pola (przecinek) w wartościach. Zwróć uwagę na inną lokalizację pliku w porównaniu z innymi przykładami.

Podgląd pliku:

First 10 rows of the CSV file with header row and with Unix-Style new line and escape char used for field delimiter.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-escape/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2,
        ESCAPECHAR = '\\'
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Slovenia';

Uwaga

To zapytanie nie powiedzie się, jeśli funkcja ESCAPECHAR nie zostanie określona, ponieważ przecinek w ciągu "Slov,enia" będzie traktowany jako ogranicznik pola zamiast części nazwy kraju/regionu. "Slov,enia" będzie traktowany jako dwie kolumny. W związku z tym konkretny wiersz zawierałby jedną kolumnę więcej niż inne wiersze, a jedną kolumnę więcej niż zdefiniowano w klauzuli WITH.

Znaki cudzysłów ucieczki

Poniższe zapytanie pokazuje, jak odczytać plik z wierszem nagłówka z nowym wierszem w stylu systemu Unix, kolumnami rozdzielanymi przecinkami i znakiem znaków z podwójnym cudzysłowem w wartościach. Zwróć uwagę na inną lokalizację pliku w porównaniu z innymi przykładami.

Podgląd pliku:

The following query shows how to read a file with a header row, with a Unix-style new line, comma-delimited columns, and an escaped double quote char within values.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-escape-quoted/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Slovenia';

Uwaga

Znak cudzysłów musi zostać uniknięta innym znakiem cudzysłów. Znak cudzysłów może pojawić się w wartości kolumny tylko wtedy, gdy wartość jest hermetyzowana znakami cudzysłów.

Pliki rozdzielane tabulatorami

Poniższe zapytanie pokazuje, jak odczytać plik z wierszem nagłówka z nowym wierszem w stylu unix i kolumnami rozdzielanymi tabulatorami. Zwróć uwagę na inną lokalizację pliku w porównaniu z innymi przykładami.

Podgląd pliku:

First 10 rows of the CSV file with header row and with Unix-Style new line and tab delimiter.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-tsv/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR ='\t',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017

Zwracanie podzbioru kolumn

Do tej pory określono schemat pliku CSV przy użyciu funkcji WITH i listę wszystkich kolumn. W zapytaniu można określić tylko kolumny, których rzeczywiście potrzebujesz, używając numeru porządkowego dla każdej wymaganej kolumny. Pominięto również kolumny bez zainteresowania.

Następujące zapytanie zwraca liczbę unikatowych nazw krajów/regionów w pliku, określając tylko potrzebne kolumny:

Uwaga

Przyjrzyj się klauzuli WITH w poniższym zapytaniu i zwróć uwagę, że na końcu wiersza zdefiniowano kolumnę [country_name] "2" (bez cudzysłowów). Oznacza to, że kolumna [country_name] jest drugą kolumną w pliku. Zapytanie zignoruje wszystkie kolumny w pliku z wyjątkiem drugiej.

SELECT
    COUNT(DISTINCT country_name) AS countries
FROM OPENROWSET(
        BULK 'csv/population/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n'
    )
WITH (
    --[country_code] VARCHAR (5),
    [country_name] VARCHAR (100) 2
    --[year] smallint,
    --[population] bigint
) AS [r]

Wykonywanie zapytań dotyczących dołączanych plików

Pliki CSV używane w zapytaniu nie powinny być zmieniane podczas uruchamiania zapytania. W długotrwałym zapytaniu pula SQL może ponowić próbę odczytu, odczytu części plików, a nawet wielokrotnie odczytywać plik. Zmiany zawartości pliku spowodują nieprawidłowe wyniki. W związku z tym pula SQL kończy się niepowodzeniem zapytania, jeśli wykryje, że czas modyfikacji dowolnego pliku zostanie zmieniony podczas wykonywania zapytania.

W niektórych scenariuszach warto odczytać pliki, które są stale dołączane. Aby uniknąć błędów zapytań z powodu stale dołączanych plików, możesz zezwolić OPENROWSET funkcji na ignorowanie potencjalnie niespójnych odczytów przy użyciu ROWSET_OPTIONS ustawienia .

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
    format = 'csv',
    parser_version = '2.0',
    firstrow = 2,
    ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}') as rows

Opcja ALLOW_INCONSISTENT_READS odczytu spowoduje wyłączenie sprawdzania czasu modyfikacji pliku w cyklu życia zapytania i odczytanie dowolnego elementu dostępnego w pliku. W dołączanych plikach istniejąca zawartość nie jest aktualizowana i dodawane są tylko nowe wiersze. W związku z tym prawdopodobieństwo nieprawidłowych wyników jest zminimalizowane w porównaniu z plikami z możliwością aktualizacji. Ta opcja może umożliwić odczytywanie często dołączanych plików bez obsługi błędów. W większości scenariuszy pula SQL po prostu ignoruje niektóre wiersze dołączane do plików podczas wykonywania zapytania.

Następne kroki

W następnych artykułach pokazano, jak wykonać następujące czynności: