Типы таблиц CONNECT CSV и FMT

Тип CSV

Многие файлы исходных данных отформатированы с полями и записями переменной длины. Самый простой формат, известный как CSV (Разделенные запятыми переменные), имеет поля столбцов, разделенные символом-разделителем. По умолчанию разделитель — запятая, но он может быть задан параметром SEP_CHAR как любой символ, например, точка с запятой.

Если первая запись файла CSV — список имен столбцов, указание параметра HEADER=1 пропустит первую запись при чтении. При записи, если файл пуст, запись с именами столбцов автоматически записывается.

Например, приведён следующий файл people.csv:

Name;birth;children
"Archibald";17/05/01;3
"Nabucho";12/08/03;2

Вы можете создать соответствующую таблицу, используя:

create table people (
  name char(12) not null,
  birth date not null date_format='DD/MM/YY',
  children smallint(2) not null)
engine=CONNECT table_type=CSV file_name='people.csv'
header=1 sep_char=';' quoted=1;

В качестве альтернативы движок может попытаться автоматически определить имена столбцов, типы данных и ширину с помощью:

create table people
engine=CONNECT table_type=CSV file_name='people.csv'
header=1 sep_char=';' quoted=1;

Для таблиц CSV параметр столбца flag — это ранг столбца в файле, начиная с 1 для левого столбца. Это позволяет отображать столбцы в другом порядке, чем в файле, и/или определять таблицу, используя только некоторые столбцы файла CSV. Например:

create table people (
  name char(12) not null,
  children smallint(2) not null flag=3,
  birth date not null flag=2 date_format='DD/MM/YY')
engine=CONNECT table_type=CSV file_name='people.csv'
header=1 sep_char=';' quoted=1;

В этом случае команда:

select * from people;

отобразит таблицу как:

Многие приложения создают файлы CSV, в которых некоторые поля заключены в кавычки, особенно потому, что текст поля содержит символ разделителя. Для таких файлов укажите параметр 'QUOTED=n', чтобы указать уровень кавычек и/или 'QCHAR=c', чтобы указать символ кавычек, который по умолчанию — ". Кавычки в одинарных кавычках должны быть указаны как QCHAR=''''. При записи поля будут заключаться в кавычки в зависимости от значения уровня кавычек, которое по умолчанию — –1 (нет кавычек):

Файлы, записанные таким образом, успешно читаются большинством приложений, включая табличные процессоры.

Примечание 1: Если указан только параметр QCHAR, параметр QUOTED будет по умолчанию равен 1.

Примечание 2: Для таблиц CSV, у которых разделителем является символ табуляции, укажите sep_char='\t'.

Примечание 3: При создании таблицы на основе существующего файла CSV вы можете позволить CONNECT проанализировать файл и создать описание столбцов. Однако это не сложный анализ файла и, например, DATE поля не будут распознаны как таковые, но будут считаться строковыми полями.

Примечание 4: Парсер CSV по умолчанию считывает и буферизует до 4 КБ на строку, строки, превышающие этот размер, будут усечены при чтении из файла. Если ожидается, что строки будут длиннее, используйте lrecl для увеличения этого размера. Например, чтобы установить максимальный размер строки в 8 КБ, используйте lrecl=8192

Ограничения для таблиц CSV

• Если secure_file_priv установлено в путь к папке, то таблицы CSV могут быть созданы только с файлами в этой папке.

Тип FMT

Таблицы FMT обрабатывают файлы различных форматов, являющиеся расширением концепции файлов CSV. CONNECT поддерживает эти файлы при условии, что все строки имеют одинаковый формат и что все поля, присутствующие во всех записях, распознаются (необязательные поля должны иметь распознаваемые разделители). Эти файлы создаются определёнными приложениями, и CONNECT обрабатывает их только в режиме чтения.

Таблицы FMT должны создаваться как таблицы CSV, задавая их тип как FMT. Кроме того, каждое описание столбца должно быть добавлено в его спецификацию формата.

Спецификация формата столбца таблиц FMT

Формат ввода для каждого столбца задаётся параметром FIELD_FORMAT. Простой пример:

IP Char(15) not null field_format=' %n%s%n',

В приведённом примере формат для этого (первого) поля — ' %n%s%n'. Обратите внимание, что пробел в начале этого формата является значимым. Пробелы в конце формата столбцов указывать не нужно.

Синтаксис и смысл формата ввода столбца — такой же, как у функции C scanf.

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

Поэтому все форматы столбцов состоят из пяти компонентов:

1. Возможная информация о том, что встречается и игнорируется перед значением столбца.
2. Маркер начала значения столбца, представленный как %n.
3. Спецификация формата самого значения столбца.
4. Маркер конца значения столбца, представленный как %n (или %m для необязательных полей).
5. Возможная информация о том, что встречается после значения столбца (недействителен, если использовался %m).

Например, используя файл funny.txt:

12345,'BERTRAND',#200;5009.13
 56, 'POIROT-DELMOTTE' ,#4256 ;18009
345 ,'TRUCMUCHE' , #67; 19000.25

Вы можете создать таблицу fmtsample с 4 столбцами ID, NAME, DEPNO и SALARY, используя оператор Create Table и форматы столбцов:

create table FMTSAMPLE (
  ID Integer(5) not null field_format=' %n%d%n',
  NAME Char(16) not null field_format=' , ''%n%[^'']%n''',
  DEPNO Integer(4) not null field_format=' , #%n%d%n',
  SALARY Double(12,2) not null field_format=' ; %n%f%n')
Engine=CONNECT table_type=FMT file_name='funny.txt';

Поле 1 — целое число (%d) с возможными начальными пробелами.

Поле 2 отделено от поля 1 необязательными пробелами, запятой и другими необязательными пробелами и находится в одинарных кавычках. Ведущая кавычка включена в компонент 1 формата столбца, за которой следует маркер %n. Значение столбца задаётся как %[^'], что означает сохранение всех считанных символов до тех пор, пока не встретится кавычка. Завершающий маркер (%n) следует за 5-м компонентом формата столбца, кавычкой, которая следует за значением столбца.

Поле 3, также разделённое запятой, представляет собой число, предваряемое знаком фунта.

Поле 4, разделённое точкой с запятой, возможно, окружённое пробелами, — число с необязательной десятичной точкой (%f).

Эта таблица будет отображаться как:

Необязательные поля

Для распознавания поле обычно должно иметь длину не менее одного символа. Например, числовое поле должно иметь не менее одной цифры, а символьное поле не может быть пустым. Однако многие существующие файлы не следуют этому формату.

Предположим, например, что предыдущий файл может быть:

12345,'BERTRAND',#200;5009.13
 56, 'POIROT-DELMOTTE' ,# ;18009
345 ,'' , #67; 19000.25

Это выведет сообщение об ошибке, например, “Неправильный формат строки x поле y FMTSAMPLE”. Чтобы избежать этого и принять эти записи, соответствующие поля должны быть указаны как «необязательные». В приведённом примере поля 2 и 3 могут иметь нулевые значения (в строках 3 и 2 соответственно). Для указания их как необязательных формат должен заканчиваться на %m (вместо второго %n). Такое утверждение может выполнить создание таблицы:

create table FMTAMPLE (
  ID Integer(5) not null field_format=' %n%d%n',
  NAME Char(16) not null field_format=' , ''%n%[^'']%m',
  DEPNO Integer(4) field_format=''' , #%n%d%m',
  SALARY Double(12,2) field_format=' ; %n%f%n')
Engine=CONNECT table_type=FMT file_name='funny.txt';

Обратите внимание, что, поскольку утверждение должно заканчиваться на %m без дополнительных символов, пропуск заключительной кавычки поля 2 был перенесён с конца второго формата столбца в начало третьего формата столбца.

Результат таблицы:

Пропущенные поля заменяются нулевыми значениями, если столбец допускает значения NULL, пробелами для строковых значений и 0 для числовых полей, если они не допускают значений NULL.

Примечание 1: Поскольку форматы указываются в кавычках, кавычки, входящие в форматы, должны быть удвоены или экранированы, чтобы избежать ошибки синтаксиса CREATE TABLE.

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

Примечание 3: Поскольку формат в основном используется для поиска подстроки, соответствующей значению столбца, спецификация поля не обязательно соответствует типу столбца. Например, предположим, что таблица содержит два столбца целых чисел, NBONE и NBTWO, и две строки, описывающие эти столбцы, могут быть:

NBONE integer(5) not null field_format=' %n%d%n',
NBTWO integer(5) field_format=' %n%s%n',

Первая строка определяет обязательное целое число (%d). Вторая строка описывает поле, которое может быть целым числом, но может быть заменено символом «-» (или любым другим). Указание формата спецификации этого столбца как символьного поля (%s ) позволяет распознать его без ошибок во всех случаях. Позже эта функция будет преобразована в целое число функцией чтения столбца, и для поля, указанного в формате как нечисловое, будет сгенерировано нулевое значение 0.

Обработка ошибок неправильных записей

При отсутствии соответствия для поля столбца процесс прерывается сообщением, например:

Bad format line 3 field 4 of funny.txt

Это также может означать, что строка ввода имеет неправильный формат или что формат столбца для этого поля был неправильно задан. Когда вы знаете, что ваш файл содержит неправильно отформатированные записи, которые следует исключить из обычной обработки, задайте параметр «maxerr» оператора CREATE TABLE, например:

Option_list='maxerr=100'

Это указывает, что для первых 100 неправильных строк не будет генерироваться сообщение об ошибке. Вы можете установить Maxerr на значение, большее, чем количество неправильных строк в ваших файлах, чтобы пропустить их и не получать сообщений об ошибках.

Кроме того, опция «accept» позволяет оставить те строки с неправильным форматом, содержащие плохо отформатированное поле, и все последующие поля записи, с нулевыми значениями. Если опция «accept» указана без «maxerr», все строки с неправильным форматом будут приняты.

Примечание: Эта обработка ошибок также применяется к таблицам CSV.

Поля, содержащие отформатированную дату

Особый случай — столбцы, содержащие отформатированную дату. В этом случае необходимо указать два формата:

1. Формат распознавания поля, используемый для разграничения даты в записи входных данных.
2. Формат даты, используемый для интерпретации даты.
3. Опция длины поля, если представление даты отличается от стандартного размера типа.

Например, предположим, что у нас есть файл исходных данных веб-журнала, содержащий записи, такие как:

165.91.215.31 - - [17/Jul/2001:00:01:13 -0400] - "GET /usnews/home.htm HTTP/1.1" 302

Заявление о создании таблицы должно быть таким:

create table WEBSAMP (
  IP char(15) not null field_format='%n%s%n',
  DATE datetime not null field_format=' - - [%n%s%n -0400]'
  date_format='DD/MMM/YYYY:hh:mm:ss' field_length=20,
  FILE char(128) not null field_format=' - "GET %n%s%n',
  HTTP double(4,2) not null field_format=' HTTP/%n%f%n"',
  NBONE int(5) not null field_format=' %n%d%n')
Engine=CONNECT table_type=FMT lrecl=400
file_name='e:\\data\\token\\Websamp.dat';

Примечание 1: Здесь, field_length=20 было необходимо, так как стандартный размер столбцов datetime составляет только 19. lrecl=400 также был указан, потому что фактический файл содержит больше информации в каждой записи, что делает рассчитанный по умолчанию размер записи слишком маленьким.

Примечание 2: Имя файла можно было указать как 'e:/data/token/Websamp.dat'.

Примечание 3: Таблицы FMT в настоящее время доступны только для чтения.