Автоматическое определение CSV

При использовании read_csv, система пытается автоматически определить способ чтения файла CSV с помощью анализатора CSV. Этот шаг необходим, потому что файлы CSV не содержат информации о формате и могут иметь различные диалекты. Автоматическое определение работает примерно следующим образом:

• Определение диалекта файла CSV (разделитель, правила обработки кавычек, escape-символ)
• Определение типов каждого столбца
• Определение наличия или отсутствия заголовка в файле

По умолчанию система пытается автоматически определить все параметры. Однако пользователь может переопределить отдельные параметры. Это может быть полезно в случае ошибки системы. Например, если неправильно выбран разделитель, его можно переопределить, вызвав read_csv с явным разделителем (например, read_csv('file.csv', delim = '|')).

Определение выполняется на основе образца файла. Размер образца можно изменить, задав параметр sample_size. По умолчанию размер образца составляет 20480 строки. Задание параметра sample_size в значение -1 означает, что для выборки будет прочитан весь файл. Способ выборки зависит от типа файла. Если мы читаем из обычного файла на диске, мы перейдем в файл и попробуем выбрать образцы из разных мест файла. Если мы читаем из файла, в котором нельзя перейти к определённому месту (например, из сжатого файла CSV или stdin), образцы берутся только с начала файла.

sniff_csv Функция

Возможно запустить анализатор CSV как отдельный шаг, используя функцию sniff_csv(filename), которая возвращает обнаруженные свойства CSV в виде таблицы с одной строкой. Функция sniff_csv принимает необязательный параметр sample_size для настройки количества взятых строк.

FROM sniff_csv('my_file.csv');
FROM sniff_csv('my_file.csv', sample_size = 1000);

Запрос

Столбец Prompt содержит SQL-команду с конфигурациями, обнаруженными анализатором.

-- use line mode in CLI to get the full command
.mode line
SELECT Prompt FROM sniff_csv('my_file.csv');

Prompt = FROM read_csv('my_file.csv', auto_detect=false, delim=',', quote='"', escape='"', new_line='\n', skip=0, header=true, columns={...});

Шаги определения

Определение диалекта

Определение диалекта происходит путём попытки парсинга образцов, используя набор рассматриваемых значений. Обнаруженный диалект — это диалект, имеющий (1) постоянное число столбцов для каждой строки и (2) наибольшее количество столбцов для каждой строки.

Для автоматического определения диалекта рассматриваются следующие диалекты.

Рассмотрим пример файла flights.csv:

FlightDate|UniqueCarrier|OriginCityName|DestCityName
1988-01-01|AA|New York, NY|Los Angeles, CA
1988-01-02|AA|New York, NY|Los Angeles, CA
1988-01-03|AA|New York, NY|Los Angeles, CA

В этом файле определение диалекта работает следующим образом:

• Если мы разделим по |, каждая строка разделяется на 4 столбцов
• Если мы разделим по ,, строки 2-4 разделены на 3 столбцов, в то время как первая строка разделена на 1 столбцов
• Если мы разделим по ;, каждая строка разделена на 1 столбцов
• Если мы разделим по \t, каждая строка разделена на 1 столбцов

В этом примере система выбирает | в качестве разделителя. Все строки разделены на одинаковое количество столбцов, и существует более одного столбца на строку, что означает, что разделитель был фактически найден в файле CSV.

Определение типов

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

Определение типов происходит путем попытки преобразовать значения в каждом столбце в кандидаты типов. Если преобразование не удается, кандидатский тип удаляется из набора кандидатских типов для этого столбца. После обработки всех образцов — выбирается оставшийся кандидатский тип с наивысшим приоритетом. Набор кандидатов типов по умолчанию приведен ниже в порядке приоритета:

Обратите внимание, что всё может быть преобразовано в VARCHAR. Этот тип имеет наименьший приоритет, то есть столбцы преобразуются в VARCHAR если их нельзя преобразовать в что-либо ещё. В flights.csv столбец FlightDate будет преобразован в DATE, а другие столбцы — в VARCHAR.

Набор кандидатов типов, которые должен учитывать читатель CSV, можно явно указать с помощью опции auto_type_candidates.

В дополнение к набору значений типов по умолчанию, другие типы, которые могут быть указаны с помощью опций auto_type_candidates:

Хотя набор типов данных, которые можно автоматически определить, может показаться довольно ограниченным, читатель CSV можно настроить для чтения произвольно сложных типов, используя опцию types описанную в следующем разделе.

Определение типа можно полностью отключить, используя опцию all_varchar . Если она установлена, все столбцы останутся как VARCHAR (как они изначально появляются в файле CSV).

Переопределение определения типа

Обнаруженные типы можно индивидуально переопределить, используя опцию types . Эта опция принимает одно из двух значений:

• Список определений типов (например, types = ['INTEGER', 'VARCHAR', 'DATE']). Это переопределяет типы столбцов в порядке их появления в файле CSV.
• В качестве альтернативы, types принимает карту name → type , которая переопределяет параметры отдельных столбцов (например, types = {'quarter': 'INTEGER'}).

Набор типов столбцов, которые можно указать с помощью опции types , не так ограничен, как типы, доступные для опции auto_type_candidates: любое допустимое определение типа приемлемо для опции types. (Чтобы получить допустимое определение типа, используйте функцию typeof() или столбец column_type результата DESCRIBE).

Поле sniff_csv() функции Column возвращает структуру с именами и типами столбцов, которые можно использовать в качестве основы для переопределения типов.

Обнаружение заголовка

Обнаружение заголовка происходит путем проверки, отличается ли строка-кандидат для заголовка от других строк в файле с точки зрения типов. Например, в flights.csv мы видим, что строка-заголовок состоит только из VARCHAR столбцов, в то время как значения содержат значение DATE для столбца FlightDate. Таким образом, система определяет первую строку как строку-заголовок и извлекает имена столбцов из строки-заголовка.

В файлах, у которых нет строки-заголовка, имена столбцов генерируются как column0, column1, и т.д.

Обратите внимание, что заголовки не могут быть обнаружены правильно, если все столбцы имеют тип VARCHAR — в этом случае система не может отличить строку-заголовок от других строк в файле. В этом случае система предполагает, что файл имеет заголовок. Это можно переопределить, установив опцию header в значение false.

Даты и метки времени

DuckDB по умолчанию поддерживает формат ISO 8601 для меток времени, дат и времён. К сожалению, не все даты и метки времени отформатированы в соответствии со стандартом. По этой причине читатель CSV также поддерживает опции dateformat и timestampformat. Используя этот формат, пользователь может указать строку формата, которая определяет, как должна быть прочитана дата или метка времени.

В рамках автоматического определения система пытается определить, хранятся ли даты и метки времени в другом представлении. Это не всегда возможно — так как существуют неоднозначности в представлении. Например, дата 01-02-2000 может быть интерпретирована как 2 января или 1 февраля. Часто эти неоднозначности можно разрешить. Например, если мы позже встретим дату 21-02-2000, то мы знаем, что формат должен быть DD-MM-YYYY. MM-DD-YYYY больше невозможен, так как месяца 21 не существует.

Если неоднозначность не удаётся разрешить, просматривая данные, система использует список предпочтений для формата даты. Если система выберет неправильный формат, пользователь может указать параметры dateformat и timestampformat вручную.

Система рассматривает следующие форматы дат (dateformat). Более высокие записи выбираются вместо более низких в случае неоднозначности (то есть ISO 8601 предпочтительнее MM-DD-YYYY).

Система рассматривает следующие форматы для временных меток (timestampformat). Более высокие записи выбираются вместо более низких в случае неоднозначности.