Что такое код fbsql_field_type

Содержание

Ошибка field ‘SQL’ not found

ошибка вылетает в на последней строчке здесь:

12.04.2015, 14:39

Ошибка: Field » not found
БД в ACCESS. Считываю и записываю данные через ADOTable по такому принципу: .

Ошибка: Field ‘Msg_text’ not found
Добрый вечер. При попытки получить значение поля Msg_text после Оптимизации вываливается ошибка.

Ошибка. adoquery field not found
Мне нужно занести в переменную b значение из Количество. Делаю так: ADOQuery2.Close;.

Ошибка Field not found DBEdit
Здравствуйте программисты я новичок в delphi, подскажите пожалуйста почему при добавление строк в.

Ошибка: Field ‘Роль’ Not found
Подскажите пожалуйста, в чем ошибка почему всегда при авторизации вылетает ошибка? procedure.

SQL — Урок 2. Типы данных

Числовые типы данных

Тип данных Объем памяти Диапазон Описание
TINYINT (M) 1 байт от -128 до 127 или от 0 до 255 Целое число. Может быть объявлено положительным с помощью ключевого слова UNSIGNED, тогда элементам столбца нельзя будет присвоить отрицательное значение. Необязательный параметр М — количество отводимых под число символов. Необязательный атрибут ZEROFILL позволяет свободные позиции по умолчанию заполнить нулями.

TINYINT — хранит любое число в диапазоне от -128 до 127.

TINYINT UNSIGNED — хранит любое число в диапазоне от 0 до 255.

TINYINT (2) — предполагается, что значения будут двузначными, но по факту будет хранить и трехзначные.

TINYINT (3) ZEROFILL — свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 002. SMALLINT (M) 2 байта от -32768 до 32767 или от 0 до 65535 Аналогично предыдущему, но с большим диапазоном.

SMALLINT — хранит любое число в диапазоне от -32768 до 32767.

SMALLINT UNSIGNED — хранит любое число в диапазоне от 0 до 65535.

SMALLINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить и пятизначные.

SMALLINT (4) ZEROFILL — свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 0002. MEDIUMINT (M) 3 байта от -8388608 до 8388608 или от 0 до 16777215 Аналогично предыдущему, но с большим диапазоном.

MEDIUMINT — хранит любое число в диапазоне от -8388608 до 8388608.

MEDIUMINT UNSIGNED — хранит любое число в диапазоне от 0 до 16777215.

MEDIUMINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить и семизначные.

MEDIUMINT (5) ZEROFILL — свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 00002. INT (M) или INTEGER (M) 4 байта от -2147683648 до 2147683648 или от 0 до 4294967295 Аналогично предыдущему, но с большим диапазоном.

INT — хранит любое число в диапазоне от -2147683648 до 2147683648.

INT UNSIGNED — хранит любое число в диапазоне от 0 до 4294967295.

INT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные.

INT (5) ZEROFILL — свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 00002. BIGINT (M) 8 байта от -2 63 до 2 63 -1 или от 0 до 2 64 Аналогично предыдущему, но с большим диапазоном.

BIGINT — хранит любое число в диапазоне от -2 63 до 2 63 -1.

BIGINT UNSIGNED — хранит любое число в диапазоне от 0 до 2 64 .

BIGINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные.

BIGINT (7) ZEROFILL — свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 0000002. BOOL или BOOLEAN 1 байт либо 0, либо 1 Булево значение. 0 — ложь (false), 1 — истина (true). DECIMAL (M,D) или DEC (M,D) или NUMERIC (M,D) M + 2 байта зависят от параметров M и D Используются для величин повышенной точности, например, для денежных данных. M — количество отводимых под число символов (максимальное значение — 64). D — количество знаков после запятой (максимальное значение — 30).

DECIMAL (5,2) — будет хранить числа от -99,99 до 99,99. FLOAT (M,D) 4 байта мин. значение +(-) 1.175494351 * 10 -39
макс. значение +(-) 3. 402823466 * 10 38 Вещественное число (с плавающей точкой). Может иметь параметр UNSIGNED, запрещающий отрицательные числа, но диапазон значений от этого не изменится. M — количество отводимых под число символов. D — количество символов дробной части.

FLOAT (5,2) — будет хранить числа из 5 символов, 2 из которых будут идти после запятой (например: 46,58). DOUBLE (M,D) 8 байт мин. значение +(-) 2.2250738585072015 * 10 -308
макс. значение +(-) 1.797693134862315 * 10 308 Аналогично предыдущему, но с большим диапазоном.

DOUBLE — будет хранить большие дробные числа.

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

Строковые типы данных

Тип данных Объем памяти Максимальный размер Описание
CHAR (M) M символов М символов Позволяет хранить строку фиксированной длины М. Значение М — от 0 до 65535.

CHAR (8) — хранит строки из 8 символов и занимает 8 байтов. Например, любое из следующих значений: », ‘Иван’,’Ирина’, ‘Сергей’ будет занимать по 8 байтов памяти. А при попытке ввести значение ‘Александра’, оно будет усечено до ‘Александ’, т.е. до 8 символов. VARCHAR (M) L+1 символов М символов Позволяет хранить переменные строки длиной L. Значение М — от 0 до 65535.

VARCHAR (3) — хранит строки максимум из 3 символов, но пустая строка » занимает 1 байт памяти, строка ‘a’ — 2 байта, строк ‘aa’ — 3 байта, строка ‘aaa’ — 4 байта. Значение более 3 символов будет усечено до 3. BLOB, TEXT L+2 символов 2 16 -1 символов Позволяют хранить большие объемы текста. Причем тип TEXT используется для хранения именно текста, а BLOB — для хранения изображений, звука, электронных документов и т.д. MEDIUMBLOB, MEDIUMTEXT L+3 символов 2 24 -1 символов Аналогично предыдущему, но с большим размером. LONGBLOB, LONGTEXT L+4 символов 2 32 -1 символов Аналогично предыдущему, но с большим размером. ENUM (‘value1’, ‘value2’, . ‘valueN’) 1 или 2 байта 65535 элементов Строки этого типа могут принимать только одно из значений указанного множества.

ENUM (‘да’, ‘нет’) — в столбце с таким типом может храниться только одно из имеющихся значений. Удобно использовать, если предусмотрено, что в столбце должен храниться ответ на вопрос. SET (‘value1’, ‘value2’, . ‘valueN’) до 8 байт 64 элемента Строки этого типа могут принимать любой или все элементы из значений указанного множества.

SET (‘первый’, ‘второй’) — в столбце с таким типом может храниться одно из перечисленных значений, оба сразу или значение может отсутствовать вовсе.

Календарные типы данных

Тип данных Объем памяти Диапазон Описание
DATE 3 байта от ‘1000-01-01’ до ‘9999-12-31’ Предназначен для хранения даты. В качестве первого значения указывается год в формате «YYYY», через дефис — месяц в формате «ММ», а затем день в формате «DD». В качестве разделителя может выступать не только дефис, а любой символ отличный от цифры.
TIME 3 байта от ‘-838:59:59’ до ‘838:59:59’ Предназначен для хранения времени суток. Значение вводится и хранится в привычном формате — hh:mm:ss, где hh — часы, mm — минуты, ss — секунды. В качестве разделителя может выступать любой символ отличный от цифры.
DATATIME 8 байт от ‘1000-01-01 00:00:00’ до ‘9999-12-31 23:59:59’ Предназначен для хранения и даты и времени суток. Значение вводится и хранится в формате — YYYY-MM-DD hh:mm:ss. В качестве разделителей могут выступать любые символы отличные от цифры.
TIMESTAMP 4 байта от ‘1970-01-01 00:00:00’ до ‘2037-12-31 23:59:59’ Предназначен для хранения даты и времени суток в виде количества секунд, прошедших с полуночи 1 января 1970 года (начало эпохи UNIX).
YEAR (M) 1 байт от 1970 до 2069 для М=2 и от 1901 до 2155 для М=4 Предназначен для хранения года. М — задает формат года. Например, YEAR (2) — 70, а YEAR (4) — 1970. Если параметр М не указан, то по умолчанию считается, что он равен 4.

Тип данных NULL

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

NOT NULL (значение не может отсутствовать) для полей логин и пароль,

NULL (значение может отсутствовать) для полей дата рождения и пол.

По умолчанию всем столбцам присваивается тип NOT NULL, поэтому его можно явно не указывать.

create table users (login varchar(20), password varchar(15), sex enum(‘man’, ‘woman’) NULL, date_birth date NULL);

Таким образом, мы создаем таблицу с 4 столбцами: логин (не более 20 символов) обязательное, пароль (не более 15 символов) обязательное, пол (мужской или женский) не обязательное, дата рождения (тип дата) необязательное.

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

Видеоуроки php + mysql

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.

Типы данных в T-SQL (Microsoft SQL Server)

Материал сегодня будет посвящен рассмотрению типов данных языка T-SQL – это язык программирования, которой используется в СУБД Microsoft SQL Server. Мы поговорим о том, какие существуют типы данных, какие у них особенности, а также в каких случаях использовать тот или иной тип данных.

Если Вы только начинаете изучать T-SQL, то на нашем сайте Вы можете найти полезные материалы для новичков на данную тему, например статьи «Справочник Transact-SQL» и «Основы программирования на T-SQL», где мы рассматривали основные моменты данного языка.

В перечисленных выше материалах мы затрагивали типы данных, но подробно о них не разговаривали, поэтому сегодня мы рассмотрим данную тему более подробно, так как понимание типов данных поможет Вам правильно спроектировать БД или написать процедуру. Например, выбор оптимального (корректного) типа данных для столбца в таблице может в будущем отразиться на размере базы (если таблица будет интенсивно расти), а именно она будет значительно меньше, чем, если бы Вы выбрали какой-то некорректный тип данных, причем это разница может достигать несколько сотен мегабайт, а то и нескольких гигабайт!

Итак, давайте начинать.

Что такое тип данных в SQL Server?

Тип данных – это характеристика, определяющая, какого рода данные будут храниться в объекте. Например: целые числа, числовые данные с плавающей запятой, данные денежного типа, дата, время, текст, двоичные данные и так далее. У каждого столбца, выражения, переменной или параметра есть определенный тип данных. В Microsoft SQL Server существует набор системных типов данных, который и определяет все доступные по умолчанию типы данных для использования. У разработчиков также существует возможность создавать псевдонимы типов данных основанные на системных типах, а также собственные пользовательские типы данных, о том, как реализовать псевдоним типа данных, мы разговаривали в материале – «Создание псевдонима типа данных в Microsoft SQL Server на T-SQL».

Типы данных в MS SQL Server делятся на следующие категории:

  • Точные числа;
  • Приблизительные числа;
  • Символьные строки;
  • Символьные строки в Юникоде;
  • Дата и время;
  • Двоичные данные;
  • Прочие типы данных.

Описание типов данных в T-SQL

Сейчас давайте рассмотрим типы данных по категориям.

Точные числа

Наименование типа Хранилище Описание
bit Если в таблице до 8 bit-столбцов 1 байт, если от 9 до 16, то 2 байта и так далее. Может принимать значения 1, 0 или NULL. Часто используется как тип данных Boolean. Строковые значения TRUE и FALSE можно преобразовать в значения данного типа: TRUE преобразуется в 1, а FALSE в 0.
tinyint 1 байт Целые числа от 0 до 255
smallint 2 байта от -2^15 (-32 768) до 2^15-1 (32 767).
int 4 байта от –2^31 (–2 147 483 648) до 2^31-1 (2 147 483 647). Это основной целочисленный тип данных в Microsoft SQL Server.
bigint 8 байт от -2^63 (-9 223 372 036 854 775 808) до 2^63-1 (9 223 372 036 854 775 807).
numeric (p, s) и decimal (p, s) Точность: от 1 до 9 = 5 байт; от 10 до 19 = 9 байт; от 20 до 28 = 13 байт; от 29 до 38 = 17 байт. Тип числовых данных с фиксированной точностью и масштабом. numeric и decimal функционально эквивалентны. p (точность) — максимальное количество десятичных разрядов числа, которые будут храниться (как слева, так и справа от десятичной запятой). Точность может быть значением в диапазоне от 1 до 38, по умолчанию 18. s (масштаб) — максимальное количество десятичных разрядов числа справа от десятичной запятой. Максимальное число цифр слева от десятичной запятой определяется как p — s (точность — масштаб). Масштаб может быть значение от 0 до p, по умолчанию 0. Максимальный размер хранилища зависит от точности. Тип данных numeric и decimal может принимать значение от –10^38+1 до 10^38–1.
smallmoney 4 байта Тип данных для хранения денежных значений с точность до одной десятитысячной денежной единицы. Число от -214 748,3648 до 214 748,3647
money 8 байт Тип данных для хранения денежных значений с точность до одной десятитысячной денежной единицы. Число от -922 337 203 685 477,5808 до 922 337 203 685 477,5807

Приблизительные числа

Наименование типа Хранилище Описание
float (n) Зависит от значения n: От 1 до 24 (7 знаков) = 4 байта; От 25 до 53 (15 знаков) = 8 байт. Используется для числовых данных с плавающей запятой. n — это количество битов, используемых для хранения мантиссы числа в формате float при экспоненциальном представлении. n определяет точность данных и размер для хранения. Может принимать значение от 1 до 53, по умолчанию 53. Диапазон значений от –1,79E +308 до 1,79E+308.
real 4 байта Используется для числовых данных с плавающей запятой. real соответствует в ISO типу float(24). Диапазон значений от –3.40E+38 до 3.40E+38.

Не рекомендуется использовать столбцы с типами float и real в предложении WHERE, так как данные типы не хранят точных значений. Также не рекомендуется использовать float и real в финансовых приложениях, в операциях, связанных с округлением. Для этого лучше использовать decimal, money или smallmoney.

Символьные строки

Наименование типа Хранилище Описание
char (n) n байт Строка с фиксированной длиной не в Юникоде, где n длина строки (от 1 до 8000). По умолчанию n = 1, если значение n не указано при использовании функций CAST и CONVERT, длина по умолчанию равна 30.
varchar ( n | max ) Размер занимаемой памяти в байтах = количество введенных символов + 2 байта. Если указать MAX, то максимально возможный размер = 2^31-1 байт (2 ГБ). Строковые данные переменной длины не в Юникоде, где n длина строки (от 1 до 8000). По умолчанию n = 1, если значение n не указано при использовании функций CAST и CONVERT, длина по умолчанию равна 30.
text Размер занимаемой памяти в байтах = количество введенных символов. Максимальный размер 2^31-1 (2 147 483 647 байт, 2 ГБ). Строка переменной длины не в Юникоде. Является устаревшим типом данных, рекомендуется использовать varchar(max).

Символьные строки в Юникоде

Наименование типа Хранилище Описание
nchar (n) n * 2 байт Строка с фиксированной длиной в Юникоде, где n длина строки (от 1 до 4000). По умолчанию n = 1, если значение n не указано при использовании в функции CAST, длина по умолчанию равна 30.
nvarchar ( n | max ) Размер занимаемой памяти в байтах = количество введенных символов, умноженное на 2 + 2 байта. Если указать MAX, то максимально возможный размер = 2^31-1 байт (2 ГБ). Строка переменной длины в Юникоде, где n длина строки (от 1 до 4000). По умолчанию n = 1, если значение n не указано при использовании в функции CAST, длина по умолчанию равна 30.
ntext Размер занимаемой памяти в байтах = количество введенных символов, умноженное на 2. Максимальный размер 2^30 — 1 (1 073 741 823 байт, 1 ГБ). Строка переменной длины в Юникоде. Является устаревшим типом данных, рекомендуется использовать nvarchar(max).

Дата и время

Наименование типа Хранилище Диапазон Точность Описание
date 3 байта От 01.01.0001 до 31.12.9999 1 день Используется для хранения даты.
datetime 8 байт От 01.01.1753 00:00:00 до 31.12.9999 23:59:59,997 0,00333 секунды Используется для хранения даты, включая время с точностью до одной трехсотой секунды.
datetime2 От 6 до 8 байт (в зависимости от точности: менее 3 цифр = 6 байт, 3-4 цифры = 7 байт, более 4 цифр = 8 байт) От 01.01.0001 00:00:00.0000000 до 31.12.9999 23:59:59.9999999 100 наносекунд Расширенный вариант типа данных datetime, имеет более широкий диапазон дат и большую точность в долях секунды (до 7 цифр).
smalldatetime 4 байта От 01.01.1900 00:00:00 до 06.06.2079 23:59:00 1 минута Сокращенный вариант типа данных datetime, имеет меньший диапазон дат и не имеет долей секунд.
time [Точность] От 3 до 5 байт От 00:00:00.0000000 до 23:59:59.9999999 100 наносекунд Используется для хранения времени дня. Точность может быть целым числом от 0 до 7, по умолчанию 7 (100 наносекунд, 5 байт). Если указать 0, то точность будет до секунды (3 байта).
datetimeoffset [Точность] От 8 до 10 байт От 01.01.0001 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 100 наносекунд Используется для хранения даты и времени, включая смещение часовой зоны относительно универсального глобального времени. Точность определяет количество знаков в дробной части секунды, данное значение может быть от 0 до 7, по умолчанию 7 (100 наносекунд, 10 байт).

Двоичные данные

Наименование типа Хранилище Описание
binary (n) n байт Двоичные данные фиксированной длины. n — значение от 1 до 8000. Если не указывать n, то значение по умолчанию 1, если не указать в функции CAST, то 30. Данный тип лучше использовать в случаях, когда размер данных, которые будут храниться в столбце, можно заранее определить.
varbinary ( n | max ) Размер занимаемой памяти в байтах = фактический размер данных + 2 байта. Если указать MAX, то максимально возможный размер = 2^31-1 байт (2 ГБ). Двоичные данные с переменной длиной. n — значение от 1 до 8000. Если не указывать n, то значение по умолчанию 1, если не указать в функции CAST, то 30. Данным типом лучше пользоваться, если размер данных в столбце заранее определить трудно. Если размер данных превышает 8000 байт, необходимо использовать тип varbinary(max).
image Максимальный размер до 2^31-1 (2 147 483 647 байт, 2 ГБ). Двоичные данные с переменной длиной. Является устаревшим типом данных, рекомендуется использовать varbinary(max).

Прочие типы данных

Наименование типа Хранилище Описание
cursor Данный тип данных можно использовать в переменных или выходных параметрах хранимых процедур, которые содержат ссылку на курсор. Тип cursor не может быть использован в инструкции CREATE TABLE, т.е. для столбца в таблице. Может принимать значение NULL.
table Особый тип данных для переменных, который предназначен для хранения результирующего набора данных. Переменные с данным типом называют – табличные переменные. Подробней о переменных с типом table мы разговаривали в материале — «Табличные переменные в Microsoft SQL Server».
sql_variant Универсальный тип данных, который может хранить значения различных типов данных. Однако sql_variant может хранить значения не всех типов, которые есть в SQL сервере, например следующие типы нельзя сохранить при помощи типа данных sql_variant: varchar(max), varbinary(max), nvarchar(max), xml, text, ntext, image, rowversion, hierarchyid, datetimeoffset, а также пространственные типы данных и определяемые пользователем типы. Тип sql_variant не может также иметь sql_variant в качестве базового типа.
rowversion (timestamp) 8 байт Тип данных rowversion представляет собой автоматически создаваемые уникальные двоичные числа. В таблице может быть определен только один столбец типа rowversion. После любого обновления строки или вставки новой строки в таблицу, которая содержит столбец типа rowversion, значение увеличенной rowversion вставляется в столбец с данным типом. Поэтому столбец с типом данных rowversion не рекомендуется использовать в ключе, особенно в первичном ключе. timestamp является синонимом типа данных rowversion, но данный синтаксис устарел и его использовать нежелательно.
xml Не более 2 ГБ. Используется для хранения XML-данных. Более детальную информацию Вы можете найти в статье «Transact-SQL — работа с XML».
uniqueidentifier 16 байт Глобальный уникальный идентификатор (GUID). Инициализировать столбец или переменную с типом uniqueidentifier можно с помощью функции NEWID или путем преобразования строки xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, где каждый x – это шестнадцатеричная цифра (0–9 или A–F).
hierarchyid Максимум 892 байта Тип данных используется для представления положения в древовидной иерархии.
Пространственные типы К пространственным типам относятся: geography – это географический пространственный тип данных, который используется для представления данных в системе координат круглой земли, geometry – это пространственный тип данных для представления данных в евклидовом пространстве (плоской системе координат).

Приоритеты типов данных в T-SQL

В Microsoft SQL Server в случаях, когда оператор объединяет два выражения с разными типами данных, происходит неявное преобразование типов, если такое преобразование не поддерживается, SQL сервер будет выдавать ошибку. Чтобы определять какой тип данных из выражений преобразовывать, SQL Server применяет правила приоритета типов данных. Тип данных, который имеет меньший приоритет, будет преобразован в тип данных с большим приоритетом. Если оба выражения имеют одинаковый тип данных, результат операции будет иметь такой же тип данных.

В MS SQL Server существует следующий приоритет типов данных:

  1. Определяемые пользователем типы данных (высший приоритет);
  2. sql_variant
  3. xml
  4. datetimeoffset
  5. datetime2
  6. datetime
  7. smalldatetime
  8. date
  9. time
  10. float
  11. real
  12. decimal
  13. money
  14. smallmoney
  15. bigint
  16. int
  17. smallint
  18. tinyint
  19. bit
  20. ntext
  21. text
  22. image
  23. timestamp
  24. unique >В MS SQL Server для совместимости со стандартом ISO существуют синонимы системных типов данных. Эти синонимы можно использовать в инструкциях языка Transact-SQL точно также как и соответствующие системные типы данных, единственный момент, что после создания объекта (таблицы, процедуры) синониму назначается базовый тип данных, связанный с этим синонимом, иными словами, каких-либо признаков, что в инструкции использовался синоним, нет.

Синонимы и соответствующие им системные типы данных представлены в таблице ниже:

Системный тип данных Синоним типа
varbinary Binary varying
varchar char varying
char character
char(1) character
char(n) character( n )
varchar(n) character varying( n )
decimal Dec
float Double precision
real float[(n)]; n = 1-7
float float[(n)]; n = 8-15
int Integer
nchar(n) national character( n )
nchar(n) national char( n )
nvarchar(n) national character varying( n )
nvarchar(n) national char varying( n )
ntext national text
rowversion timestamp

Распространенные ошибки при выборе типа данных в T-SQL

В начале статьи я говорил, что выбор неоптимального типа данных может сказаться на размере базы данных, так вот одной из самых распространенных ошибок при проектировании таблицы является выбор для столбца, который должен содержать тип данных Boolean (т.е. 0 или 1), тип SMALLINT или INT. Как Вы уже поняли, такого типа данных как Boolean в T-SQL нет, поэтому для этих целей разработчики используют похожие (подходящие) типы данных и в большинстве случаев их выбор неправильный. Если Вам нужно хранить только значения 0 или 1 (т.е. как Boolean), то в T-SQL существует специальный тип данных BIT, SQL сервер выделяет для хранения всего 1 байт, но в отличие от типа TINYINT, под который также отводится 1 байт, SQL сервер оптимизирует хранение бит столбцов. Если таблица содержит не больше 8 бит столбцов, столбцы хранятся как 1 байт, если таких столбцов от 9 до 16, то 2 байта и т.д.

Для сравнения давайте посмотрим на разницу.

Таблица 1

Таблица 2 (с использованием BIT столбцов)

Сравнение

Количество строк Размер в мегабайтах (MB)
Таблица 1 Таблица 2 (с использованием BIT столбцов) Разница
1 000 0,02 0,01 0,01
10 000 0,15 0,09 0,07
100 000 1,53 0,86 0,67
1 000 000 15,26 8,58 6,68
10 000 000 152,59 85,83 66,76
100 000 000 1525,88 858,31 667,57

Как видите, после добавления нескольких миллионов строк разница будет ощутимая, и это на простой, маленькой, тестовой таблице.

Про типы данных Microsoft SQL Server у меня все, надеюсь, материал был Вам полезен, для более детального изучения языка T-SQL и SQL сервера в целом, рекомендую почитать мою книгу «Путь программиста T-SQL», удачи!

fbsql_field_type

(PHP 4 >= 4.0.6, PHP 5, PHP 7)

fbsql_field_type — Get the type of the specified field in a result

Описание

fbsql_field_type() is similar to the fbsql_field_name() function, but the field type is returned instead.

Список параметров

Идентификатор результата запроса, полученный из fbsql_query() или fbsql_db_query() .

The numerical offset of the field. The field index starts at 0.

Возвращаемые значения

Returns the field type, as a string.

This can be one of int, real, string, blob, and others as detailed in the » FrontBase documentation.

Примеры

Пример #1 fbsql_field_type() example

( «localhost» , «_SYSTEM» , «» );
fbsql_select_db ( «wisconsin» );
$result = fbsql_query ( «SELECT * FROM onek;» );
$fields = fbsql_num_fields ( $result );
$rows = fbsql_num_rows ( $result );
$i = 0 ;
$table = fbsql_field_table ( $result , $i );
echo «Your ‘» . $table . «‘ table has » . $fields . » fields and » . $rows . » records
» ;
echo «The table has the following fields
» ;
while ( $i $fields ) <
$type = fbsql_field_type ( $result , $i );
$name = fbsql_field_name ( $result , $i );
$len = fbsql_field_len ( $result , $i );
$flags = fbsql_field_flags ( $result , $i );
echo $type . » » . $name . » » . $len . » » . $flags . «
» ;
$i ++;
>
fbsql_close ();

Смотрите также

  • fbsql_field_name() — Get the name of the specified field in a result

SQL*Loader: загрузка в базу данных Oracle и преобразование

В этой статье моего блога мы поговорим о способах загрузки информации в базу данных Oracle, а именно рассмотрим утилиту импорта данных в СУБД Оракл под названием SQL*Loader.

Загрузка и преобразование данных

Одной из наиболее типичных задач администратора баз данных Oracle (как и любой другой) является загрузка данных из внешних источников. Хотя обычно она выполняется при первоначальном заполнении базы данных, нередко загружать данные в различные таблицы требуется на протяжении всего существования производственной базы данных. Раньше администраторы баз данных применяли для выполнения загрузки данных из плоских файлов в таблицы базы данных Oracle только утилиту SQL*Loader.

Сегодня, хотя утилита SQL*Loader по-прежнему остается важным инструментом для загрузки данных в базы данных Oracle, компания Oracle также предлагает и другой способ для загрузки таблиц, а именно — рекомендует применять механизм внешних таблиц. Внешние таблицы используют функциональные возможности SQL*Loader и позволяют перед загрузкой данных в базу данных выполнять над ними сложные преобразования. С их помощью данные можно не только загружать в базу, но и выгружать во внешние файлы и затем из этих файлов загружать их в другие базы данных Oracle.

Во многих случаях, особенно в хранилищах данных, загружаемые данные требуется преобразовывать. Oracle предлагает несколько средств для выполнения преобразования данных внутри базы данных, в число которых входят технологии SQL и PL/SQL. Например, мощная конструкция MODEL позволяет создавать сложные многомерные массивы и производить сложные вычисления между строками и массивами с помощью простого SQL-кода.

А еще Oracle предлагает полезный механизм репликации данных под названием Oracle Streams (Потоки Oracle), который позволяет передавать изменения из одной базы данных в другую. Этот механизм можно использовать для различных целей, в том числе и для обслуживания резервной базы данных.

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

Краткий обзор процесса извлечения, преобразования и загрузки данных

Прежде чем запускать в отношении СУБД Oracle Database какое-либо приложение, ее сначала необходимо заполнить данными. Одним из наиболее типичных источников данных для заполнения базы является набор плоских файлов из унаследованных систем или других источников.

Раньше применение стандартного или прямого метода загрузки данных с использованием SQL*Plus было единственно возможным способом выполнения загрузки этих данных из внешних файлов в таблицы базы данных. С технической точки зрения, SQL*Loader по-прежнему остается главной поставляемой Oracle утилитой для выполнения загрузки данных из внешних файлов, но при желании можно также использовать и механизм внешних таблиц, который за счет применения инструмента SQL*Loader помогает получать доступ к данным, находящимся во внешних файлах данных.

Из-за того, что исходные данные могут содержать излишнюю информацию или данные в другом формате, отличном от того, который требуется приложению, зачастую бывает необходимо преобразовывать их каким-нибудь образом прежде, чем база данных сможет их использовать. Преобразование данных является особенно распространенным требованием для хранилищ данных, которые предусматривают извлечение данных из множества источников. Существует возможность производить предварительное или базовое преобразование исходных данных во время запуска самой утилиты SQL*Loader. Однако сложное преобразование данных требует отдельных этапов, и для управления этим процессом на выбор доступно нескольких приемов. В большинстве хранилищ данные проходят три основных этапа, прежде чем их становится можно анализировать: извлечение (extraction), преобразование (transformation) и загрузка (loading), все месте называемые процессом ETL. Ниже описано, что собой представляет каждый из этапов.

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

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

Сегодня в Oracle Database 11g предлагаются просто потрясающие возможности ETL, которые позволяют производить загрузку данных в базу данных новым образом, а именно — методом выполнения их преобразования во время загрузки. За счет использования базы данных Oracle для выполнения всех этапов ETL все обычно трудоемкие процессы ETL можно выполнять довольно легко. Oracle предоставляет целый набор вспомогательных инструментов и технологий, которые сокращают время, требуемое на выполнение загрузки данных в базу данных, и при этом также упрощают всю связанную с этим работу. В частности, в состав предлагаемого Oracle решения ETL входят следующие компоненты.

  • Внешние таблицы (external tables). Внешние таблицы предоставляют способ для объединения процессов загрузки и преобразования. Их применение позволит не прибегать к громоздким и отнимающим много времени промежуточным таблицам во время загрузки данных. О них более подробно будет рассказываться позже в этой главе, в разделе “Использование внешних таблиц для загрузки данных”.
  • Многотабличные вставки (multitable inserts). Механизм многотабличных вставок позволяет вставлять данные не в одну, а сразу в несколько таблиц одновременно с использованием разных критериев для разных таблиц. Он устраняет необходимость в выполнении такого дополнительно шага, как разбиение данных на отдельные группы, перед осуществлением их загрузки. О нем более подробно речь пойдет позже в этой главе, в разделе “Применение многотабличных вставок”.
  • Вставки-обновления (upserts). Это выдуманное название для обозначения технологии, позволяющей либо вставлять данные в таблицу, либо лишь обновлять в ней строки с помощью одного SQL-оператора MERGE. Оператор MERGE будет либо вставлять новые данные, либо обновлять строки, если такие данные уже существуют в таблице. Он может очень сильно облегчать процесс загрузки, поскольку избавляет от необходимости беспокоиться о том, не содержатся ли в таблице уже такие данные. Более подробно о нем будет рассказываться позже в этой главе, в разделе “Применение оператора MERGE”.
  • Табличные функции (table functions). Табличные функции генерируют в качестве вывода набор строк. Они возвращают экземпляр типа коллекции (т.е. вложенной таблицы или одного из типов данных VARRAY). Они похожи на представления, но вместо определения процесса преобразования декларативным образом на языке SQL подразумевают его определение процедурным образом на языке PL/SQL. Табличные функции очень помогают при выполнении крупных и сложных преобразований, поскольку позволяют выполнять их перед загрузкой данных в хранилище данных. Более подробно о них речь пойдет позже в этой главе, в раздел “Применение табличных функций для преобразования данных”.
  • Переносимые табличные пространства (transportable tablespaces). Эти табличные пространства обеспечивают эффективный и скоростной способ для перемещения данных из одной базы данных в другую. Например, с их помощью можно легко и быстро осуществлять миграцию данных между базой данных OLTP и хранилищем данных. О них более подробно мы поговорим в новых статьях блога.

На заметку! Еще для осуществления загрузки данных эффективным образом можно использовать инструмент Oracle Warehouse Builder (Программа для построения хранилищ данных Oracle) или просто OWB. Этот инструмент представляет собой управляемое мастером средство для загрузки данных в базу данных через SQL*Loader. Он позволяет загружать данные как из баз данных Oracle, так и из плоских файлов. Кроме того, он позволяет извлекать данные и из других баз данных, наподобие Sybase, Informix и Microsoft SQL Server, через механизм Oracle Transparent Gateways (Прозрачные шлюзы Oracle). Он сочетает в себе функции для ETL и проектирования в очень удобном для использования формате.

В следующем разделе вы узнаете о том, как можно использовать утилиту SQL*Loader для загрузки данных из внешних файлов. Это также поможет разобраться и в том, как применять внешние таблицы для выполнения загрузки данных. После описания механизма внешних таблиц будут рассмотрены различные предлагаемые в Oracle Database 11g методы для преобразования данных.

Использование утилиты SQL*Loader

Утилита SQL*Loader, которая поставляется с сервером баз данных Oracle, часто применяется администраторами баз данных для загрузки внешних данных в базы данных Oracle. Она является чрезвычайно мощным инструментом и способна выполнять не только загрузку данных из текстовых файлов. Ниже приведен краткий перечень других ее возможностей.

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

Утилита SQL*Loader может применяться для выполнения загрузки данных в нескольких режимах.

  • В обычном режиме (conventional data loading). В этом режиме SQL*Loader считывает за раз по несколько строк и сохраняет их в массиве связывания (bind array), а впоследствии вставляет весь этот массив сразу в базу данных и фиксирует операцию.
  • В прямом режиме (direct-path loading). В этом режиме никакой SQL-оператор INSERT для загрузки данных в таблицы Oracle не используется. Вместо этого из подлежащих загрузке данных создаются структуры массивов столбцов, которые затем применяются для форматирования блоков данных Oracle, после чего те напрямую записываются в таблицы базы данных.
  • В режиме внешних таблиц (external data loading). Новый предлагаемый Oracle механизм внешних таблиц базируется на использовании функциональных возможностей SQL*Loader и позволяет получать доступ к находящимся во внешних файлах данным так, будто бы они являются частью таблиц базы данных. При применении драйвера доступа ORACLE_LOADER для создания внешней таблицы, по сути, используются функциональные возможности SQL*Loader. В Oracle Database 11g еще предлагается новый драйвер доступа ORACLE_DATAPUMP, который позволяет осуществлять запись во внешние таблицы.

Два первых режима (или метода) загрузки имеют как свои преимущества, так и свои недостатки. Из-за того, что прямой режим загрузки работает в обход предлагаемого Oracle механизма SQL, он является гораздо быстрее обычного режима загрузки. Однако в том, что касается возможностей для преобразования данных, обычный режим значительно превосходит прямой, поскольку позволяет применять к столбцам таблицы во время загрузки данных ряд различных функций. Поэтому в Oracle рекомендуют применять обычный метод загрузки для загрузки небольшого количества данных, а прямой — для загрузки данных большого объема. Прямой режим загрузки будет более детально рассматриваться сразу же после изучения основных функциональных возможностей SQL*Loader и способов применения обычного метода загрузки. Что касается режима внешних таблиц, то о нем более подробно речь пойдет позже в этой главе, в разделе “Использование внешних таблиц для загрузки данных”.

Процесс загрузки данных с помощью утилиты SQL*Loader включает в себя два основных шага.

  1. Выбор файла данных, в котором содержатся подлежащие загрузке данные. Такой файл обычно имеет расширение .dat и содержит необходимые данные. Эти данные могут быть в нескольких форматах.
  2. Создание управляющего файла (control file). Управляющий файл указывает SQL*Loader, как следует размещать поля данных в таблице Oracle и не нужно ли преобразовывать данные каким-нибудь образом. Такой файл обычно имеет расширение .ctl.

Управляющий файл будет предоставлять схему отображения столбцов таблицы на поля данных во входном файле. Наличие отдельного файла данных для выполнения загрузки вовсе не является обязательным требованием. При желании данные могут включаться и в сам управляющий файл, после указания информации, касающейся управления процессом загрузки, наподобие списка полей и т.д. Эти данные могут предоставляться как в виде полей фиксированной длины, так и в свободном формате с использованием специального символа-разделителя, вроде запятой (,) или конвейера (|). Раз управляющий файл является столь важным, давайте начнем именно с него.

Изучение управляющего файла SQL*Loader

Управляющий файл SQL*Loader представляет собой простой текстовый файл, в котором указываются важные детали связанного с загрузкой задания, наподобие места размещения исходного фала данных, а также схема отображения данных в этом исходном файле на столбцы в целевой таблице. Еще в нем могут указываться любые подлежащие выполнению во время процесса загрузки операции преобразования, а также имена файлов журналов, которые должны использоваться для загрузки, и имена файлов, которые должны использоваться для перехвата неправильных и отвергаемых данных. В общем, в управляющем файле утилите SQL*Loader предоставляются инструкции касательно следующих аспектов:

  • источник данных, подлежащих загрузке в базу;
  • спецификация столбцов в целевой таблице;
  • особенности используемого во входном файле форматирования;
  • отображение полей входного файла на столбцы целевой таблицы;
  • правила преобразования данных (применение функций SQL);
  • место размещения файлов журналов и файлов ошибок.

В листинге 13.1 приведен пример типичного управляющего файла SQL*Loader. Утилита SQL*Loader считает содержащиеся в исходных файлах строки данных записями (records), и потому в управляющем файле еще может указываться формат записей. Обратите внимание на то, что также допускается использовать и отдельный файл для данных. В данном примере, однако, подлежащие загрузке данные идут прямо следом за управляющей информацией, благодаря использованию в управляющем файле спецификации INFILE *. Эта спецификация указывает, что данные для загрузки будут идти после управляющей информации. При выполнении разовой операции по загрузке данных, пожалуй, лучше поступать как можно проще и размещать данные в самом управляющем файле. Ключевое слово BEGINDATA показывает SQL*Loader, где начинается содержащая данные часть управляющего файла.

Та часть управляющего файла, в которой описываются поля данных, называется списком полей (field list). В управляющем файле, показанном в листинге 13.1, этот список полей выглядит так:

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

В управляющем файле можно задавать множество переменных, которые, грубо говоря, делятся на следующие группы:

  • конструкции, касающиеся загрузки;
  • конструкции, касающиеся файлов данных;
  • конструкции, касающиеся отображения таблиц и полей;
  • задаваемые в управляющем файле параметры командной строки.

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

Совет. При отсутствии уверенности в том, какие параметры использовать для сеанса SQL*Loader, можно просто ввести в командной строке операционной системы sqlldr для просмотра всех доступных вариантов. Выполнение этой команды будет приводить к отображению списка всех возможных параметров и значений по умолчанию, которые приняты для них в конкретной операционной системе (если таковые существуют).

Конструкции, касающиеся загрузки

Ключевые слова LOAD DATA находятся в самом начале управляющего файла и просто означают, что требуется выполнить загрузку данных из входного файла в таблицы Oracle с помощью SQL*Loader.

Конструкция INTO TABLE указывает, в какую таблицу требуется загрузить данные. При необходимости выполнить загрузку данных сразу в несколько таблиц, нужно использовать по одной конструкции INTO TABLE для каждой таблицы. Ключевые слова INSERT, REPLACE, и APPEND указывают базе данных, каким образом должна выполняться загрузка. В случае применения конструкции INSERT таблица должна обязательно быть пустой, иначе процесс загрузки будет приводить к генерации ошибки или останавливаться. Конструкция REPLACE указывает Oracle усекать таблицу и начинать загружать новые данные. При выполнении операции загрузки с использованием параметра REPLACE зачастую будет казаться, будто бы она сначала зависает. На самом же деле в это время Oracle занимается усечением таблицы перед началом процесса загрузки. Что касается конструкции APPEND, то она указывает Oracle добавлять новые строки к существующим данным таблицы.

Конструкции, касающиеся файлов данных

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

Спецификация файла данных

Имя и место размещения входного файла указывается с помощью параметра INFILE:

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

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

Физические и логические записи

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

Для преобразования этих трех физических записей можно использовать в управляющем файле либо конструкцию CONCATENATE, либо конструкцию CONTINUEIF.

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

Конкретно эта конструкция CONCATENATE определяет, что одна логическая запись должна получаться путем объединения четырех строк данных. Если каждая строка данных состоит из 80 символов, тогда получается, что в создаваемой новой логической записи будет содержаться 320 символов. Из-за этого при использовании конструкции CONCATENATE следует также задавать вместе с ней и конструкцию длины записи (RECLEN). В данном случае эта конструкция должна выглядеть так:

Что касается конструкции CONTINUEIF, то она позволяет объединять физические записи в логические за счет указания одного или более символов в конкретном месте. Например:

В этом примере конструкция CONTINUEIF указывает, что в случае обнаружения в начале строки четырех символов next, утилита SQL*Loader должна воспринимать все последующие данные как продолжение предыдущей строки (четыре символа и слово next были выбраны произвольно: в роли указателей продолжения могут выступать любые символы).

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

Здесь конструкция CONTINUEIF определяет, что в случае обнаружения в конце строки символа амперсанда (&), утилита SQL*Loader должна воспринимать следующую строку как продолжение предыдущей.

На заметку! Применение как конструкции CONTINUEIF, так и конструкции CONCATENATE будет замедлять работу SQL*Loader, поэтому все-таки лучше отображать физические и логические записи по схеме “один к одному”. Так следует поступать потому, что при объединении нескольких физических записей для образования одной логической SQL*Loader требуется выполнять дополнительное сканирование входных данных, что отнимает больше времени.

Формат записей

Для записей можно применять один из трех следующих форматов.

  • Потоковый формат (stream format). Этот формат является самым распространенным и подразумевает использование специального символа завершения для обозначения конца записи. При сканировании входного файла утилита SQL*Loader знает, что достигла конца записи, когда наталкивается на такой символ завершения. Если символ завершения не задан, по умолчанию таковым считается символ новой строки или символ перехода на новую строку (которому в Windows должен также обязательно предшествовать символ возврата каретки). В наборе из трех записей, который приводился в предыдущем примере, применялся именно такой формат.
  • Переменный формат (variable format). Этот формат подразумевает явное указание в начале каждой записи ее длины, как показано в следующем примере:

В этой строке содержится две записи: первая длиной в шесть символов (sammy) и вторая длиной в двенадцать символов (johnson,1234). Конструкция var 2 указывает SQL*Loader, что все записи данных имеют переменный размер, и что перед каждой новой записью идет показатель ее размера в виде поля длиной в 2 символа.

  • Фиксированный формат (fixed format). Этот формат подразумевает задание конкретного фиксированного размера для всех записей. Ниже приведен пример, в котором указывается, что каждая запись занимает 12 байтов в длину:

В этом примере, на первый взгляд, кажется, что запись включает в себя всю строку (sammyy,1234, johnso,1234), но конструкция fix 12 указывает, что на самом деле в этой строке содержится целых две записи по 12 символов. Следовательно, получается, что в случае применения фиксированного формата в исходном файле данных допускается иметь и по несколько записей в каждой строке.

Конструкции, касающиеся сопоставления таблиц и полей

Во время сеанса загрузки SQL*Loader берет поля данных из записей данных и преобразует их в столбцы таблицы. Именно в этом процессе и помогают конструкции, касающиеся сопоставления таблиц и полей. С их помощью в управляющем файле предоставляются детали о полях, в том числе имена столбцов, позиция, типы содержащихся во входных записях данных, разделители и параметры преобразования.

Имя столбца в таблице

Каждый столбец в таблице задается четким образом с указанием позиции и типа данных, которые ассоциируются со значением соответствующего поля во входном файле. Загружать значениями все столбцы в таблице не обязательно. В случае пропуска каких-нибудь столбцов в управляющем файле, для них автоматически устанавливается значение NULL.

Позиция

Утилите SQL*Loader требуется как-то узнавать, где во входном файле находятся различные поля. Полями (fields) называются отдельные элементы в файле данных, и никакого прямого соответствия между этими полями и столбцами в таблице, в которую загружаются данные, не существует. Процесс отображения полей во входном файле данных на столбцы таблицы в базе данных называется установкой полей (field setting), и отнимает больше всего времени у ЦП во время загрузки. Точную позицию различных полей в записи данных позволяет задавать конструкция POSITION. Указываемая в этой конструкции позиция может быть относительной или абсолютной.

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

В этом примере конструкция POSITION инструктирует SQL*Loader сначала загружать первое поле employee_id, а затем переходить к загрузке поля employee_name, которое начинается в позиции 7 и занимает 30 символов в длину.

Под указанием абсолютной позиции подразумевается указание просто позиции, в которой начинается и заканчивается каждое поле:

Типы данных

Типы данных, используемые в управляющем файле, касаются только входных записей, и с теми, которые ассоциируются со столбцами в таблицах базы данных, они не совпадают. Ниже перечислены четыре основных типа данных, которые могут применяться в управляющем файле SQL*Loader:

  • INTEGER(n) — двоичное целое число, где n может быть 1, 2, 4 или 8
  • SMALLINT
  • CHAR
  • INTEGER EXTERNAL
  • FLOAT EXTERNAL
  • DECIMAL EXTERNAL

Разделители

После указания типов данных можно задавать разделитель (delimiter), который должен использоваться для разделения полей. Определять его можно с помощью либо конструкции TERMINATED BY, либо конструкции ENCLOSED BY.

Конструкция TERMINATED BY ограничивает поле указанным символом и обозначает конец поля. Ниже приведены примеры:

В первом примере конструкция TERMINATED BY указывает, что концом поля должен служить первый встречаемый символ пробела, а во втором — что для разделения полей должен использоваться символ запятой.

Конструкция ENCLOSED BY » » указывает, что в роли символа разделения полей должна выступать пара двойных кавычек. Ниже приведен пример использования этой конструкции:

Совет. В Oracle рекомендуют избегать использования разделителей и лучше указывать везде, где можно, позиции полей (посредством параметра POSITION). Указание позиций полей избавляет базу данных от необходимости сканировать файл данных и отыскивать там выбранные разделители, что, следовательно, сокращает затрачиваемое на его обработку время.

Параметры преобразования данных

При желании можно делать так, чтобы перед загрузкой данных полей в столбцы таблицы к ним применялись SQL-функции. Вообще для преобразования значений полей разрешается использовать только такие SQL-функции, которые возвращают одиночные значения. На поле внутри строки SQL требуется ссылаться с помощью синтаксиса имя_поля. Саму SQL-функцию или функции нужно указывать после указания ассоциируемого с полем типа данных и заключать в двойные кавычки, как показано в следующих примерах:

Как не трудно заметить, применение SQL-операций и функций к значениям полей перед их загрузкой в таблицы помогает осуществлять преобразование данных прямо во время их загрузки.

Задаваемые в управляющем файле параметры командной строки

Утилита SQL*Loader позволяет задавать в командной строке ряд параметров времени выполнения при вызове ее исполняемого файла. Параметры, значения которых должны оставаться одинаковыми для всех заданий, обычно задаются в отдельном файле параметров, позволяющем далее использовать командную строку просто для запуска заданий SQL*Loader, как в интерактивном режиме, так и в виде запланированных пакетных заданий. В командной строке указываются лишь те параметры, которые касаются конкретно времени выполнения, вместе с именем и местом размещения управляющего файла.

В качестве альтернативного варианта, параметры времени выполнения еще можно также указывать и внутри самого управляющего файла с применением конструкции OPTIONS. Да, параметры времени выполнения всегда можно задавать при вызове SQL*Loader, но если они будут часто повторяться, тогда все-таки лучше указывать их в управляющем файле с помощью конструкции OPTIONS. Использовать конструкцию OPTIONS особенно удобно в случае, когда длина информации, подлежащей указанию в командной строке SQL*Loader, настолько велика, что превышает принятый в операционной системе максимальный размер командной строки.

На заметку! Задание параметра в командной строке будет приводить к переопределению тех значений, которые были указаны для него в управляющем файле.

В следующих подразделах описываются некоторые наиболее важные параметры, которые можно задавать в управляющем файле конструкцией OPTIONS.

Параметр USER >Параметр USERID позволяет указывать имя и пароль того пользователя в базе данных, который обладает необходимыми для осуществления загрузки данных привилегиями:

Параметр CONTROL

Параметр CONTROL позволяет указывать имя управляющего файла, который должен использоваться для сеанса SQL*Loader. Этот управляющий файл может включать в себя спецификации всех параметров загрузки. Конечно, выполнять загрузку данных можно и при помощи вводимых вручную команд, но применение управляющего файла обеспечивает большую гибкость и позволяет автоматизировать процесс загрузки.

Параметр DATA

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

Параметры BINDSIZE и ROWS

Параметры BINDSIZE и ROWS позволяют задавать размер, который должен иметь массив связывания в обычном режиме загрузки. При выполнении загрузки в обычном режиме утилита SQL*Loader не вставляет данные в таблицу строка за строкой. Вместо этого она вставляет в таблицу сразу целый набор строк; этот набор строк и называется массивом связывания (bind array), и за его размер отвечает либо параметр BINDSIZE, либо параметр ROWS.

Параметр BINDSIZE задает размер массива связывания в байтах. В системе автора этот размер по умолчанию составлял 256 000 байт.

Что касается параметра ROWS, то он не накладывает никаких ограничений на количество байтов в массиве связывания. Вместо этого он накладывает ограничение на количество строк, которое может содержаться в каждом массиве связывания; SQL*Loader перемножает это значение в параметре ROWS на подсчитанное значение размера каждой строки в таблице. В системе автора количество строк в параметр ROWS по умолчанию составляло 64.

На заметку! В случае задания значений и для параметра BINDSIZE, и для параметра ROWS, утилита SQL*Loader использует для массива связывания меньшее из этих двух значений.

Параметр DIRECT

В случае указания для параметра DIRECT значения true (DIRECT=true) утилита SQL*Loader использует прямой режим загрузки, а не обычный. По умолчанию для этого параметра устанавливается значение false (DIRECT=false), означающее, что по умолчанию должен использоваться обычный режим загрузки.

Параметр ERRORS

Параметр ERRORS позволяет указывать, сколько максимум ошибок может происходить, прежде чем выполнение задания SQL*Loader должно завершаться. В большинстве систем для этого параметра по умолчанию устанавливается значение 50. При нежелании терпеть никакие ошибки, можно устанавливать для этого параметра значение :

Параметр LOAD

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

Параметр LOG

Параметр LOG позволяет указывать имя файла журнала (log file), который утилита SQL*Loader должна использовать в процессе загрузки. Файл журнала, который будет показан чуть позже, предоставляет массу полезной информации о сеансе SQL*Loader.

Параметр BAD

Параметр BAD позволяет указывать имя и место размещения файла некорректных записей (bad file). В случае отклонения каких-то записей из-за наличия в них ошибок, связанных с форматированием данных, утилита SQL*Loader будет заносить все эти записи в упомянутый файл. Например, размер поля может превышать заданную для него длину и, как следствие, отвергаться утилитой SQL*Loader. Обратите внимание на то, что записи могут отклоняться не только утилитой SQL*Loader, но и самой базой данных. Например, при попытке вставить строки с дублированными значениями первичных ключей, база данных будет отклонять их вставку. Такие записи тоже будут помещаться в файл некорректных записей. В случае не указания имени файла некорректных записей явным образом, Oracle будет создавать такой файл автоматически и использовать для него принятое по умолчанию имя с именем управляющего файла в качестве префикса.

Параметр SILENT

По умолчанию SQL*Loader отображает на экране ответные сообщения и тем самым информирует о ходе процесса загрузки. При желании отображение этих сообщений можно отключать с помощью параметра SILENT. Для этого параметра можно устанавливать несколько значений. Например, отключать отображение всех видов сообщений можно путем установки для него значения ALL:

Параметры DISCARD и DISCARDMAX

Все записи, которые отвергаются во время загрузки из-за несоответствия указанным в управляющем файле критериями выбора записей, помещаются в файл отвергнутых записей (discard file). По умолчанию этот файл не создается. Oracle будет создавать его только при наличии отвергнутых записей, да и то лишь в том случае, если он был явно задан в управляющем файле. Для указания в управляющем файле имени и места размещения файла отвергнутых записей как раз и применяется параметр DISCARD, например:

По умолчанию SQL*Loader не накладывает никаких ограничений на количество записей; следовательно, отвергаться могут и все логические записи. Посредством параметра DISCARDMAX, однако, можно брать и ограничивать количество отвергаемых записей.

Совет. Как в файл некорректных, так и в файл отвергнутых записей, все записи помещаются в своем исходном формате. Это позволяет легко, особенно во время загрузки больших объемов данных, редактировать эти файлы надлежащим образом и использовать их для повторной загрузки данных, которые не удалось загрузить во время первоначального сеанса загрузки.

Параметр PARALLEL

Параметр PARALLEL позволяет указывать, разрешено ли SQL*Loader запускать по несколько параллельных сеансов при выполнении загрузки в прямом режиме:

Параметр RESUMABLE

С помощью параметра RESUMABLE можно включать предлагаемую Oracle функцию Resumable Space Allocation (возобновление функционирования после устранения проблемы выделения пространства). Если эта функция включена, то при возникновении во время загрузки данных проблем с пространством, выполнение задания будет просто приостанавливаться, и тогда администратор сможет получать соответствующее уведомление и выделять больше пространства, чтобы выполнение задания могло продолжиться без проблем. О функции Resumable Space Allocation более подробно рассказывалось в главе 8. По умолчанию для параметра RESUMABLE устанавливается значение false, означающее, что функция Resumable Space Allocation отключена. Для ее включения достаточно просто установить для этого параметра значение true (RESUMABLE=true).

Параметр RESUMABLE_NAME

Параметр RESUMABLE_NAME позволяет указывать конкретное задание по загрузке, которое должно поддаваться возобновлению при использовании функции Resumable Space Allocation. По умолчанию устанавливаемое для него значение образуется путем объединения имени пользователя, идентификатора сеанса и идентификатора экземпляра.

Параметр RESUMABLE_TIMEOUT

Параметр RESUMABLE_TIMEOUT может задаваться только в случае установки для параметра RESUMABLE значения true. Он позволяет определять таймаут, т.е. максимальное время, на которое выполнение операции может откладываться в случае столкновения со связанной с выделением пространства проблемой. При невозможности устранить проблему в пределах этого времени, выполнение операции будет прерываться. По умолчанию этот таймаут составляет 7 200 секунд.

Параметр SKIP

Параметр SKIP очень удобно использовать в ситуациях, когда SQL*Loader прерывает выполнение задания из-за каких-то ошибок, но уже успевает зафиксировать некоторые строки. Он позволяет пропускать определенное количество строк во входном файле при выполнении задания SQL*Loader во второй раз. Альтернативным вариантом является усечение таблицы и перезапуск задания SQL*Loader с самого начала, который не очень удобен, если в таблицы базы данных уже было загружено приличное количество строк.

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

Генерирование данных во время загрузки

Утилита SQL*Loader позволяет генерировать данные для загрузки столбцов. Это означает, что выполнять загрузку можно и без всякого использования файла данных. Чаще всего, однако, данные генерируются лишь для одного или нескольких столбцов при выполнении общей загрузки из файла данных. Ниже приведен перечень тех типов данных, которые позволяет генерировать SQL*Loader.

  • Постоянное значение (constant value). С использованием конструкции CONSTANT можно устанавливать для столбца постоянное значение. Например, в следующем примере эта конструкция указывает, что все строки, заполняемые во время данного сеанса, должны иметь в столбце loaded_by значение sysadm:
  • Значение выражения (expression value). С помощью конструкции EXPRESSION можно устанавливать для столбца значение SQL-операции или функции PL/SQL, как показано ниже:
  • Номер записи в файле данных (datafile record number). С применением конструкции RECNUM можно устанавливать для столбца в качестве значения номер записи, приведшей к загрузке данной строки:
  • Системная дата (system date). Посредством переменной sysdate можно устанавливать для столбца в качестве значения дату выполнения загрузки данных:
  • Последовательность (sequence). С помощью функции SEQUENCE можно генерировать уникальные значения для загрузки столбца. В следующем примере эта функция указывает, что должно использоваться текущее максимальное значение последовательности loadseq и что это значение при каждой вставке строки должно увеличиваться на единицу:

Вызов SQL*Loader

Вызывать утилиту SQL*Plus можно несколькими способами. Стандартный синтаксис для вызова SQL*Loader выглядит следующим образом:

Ниже приведен пример вызова SQL*Loader:

На заметку! При вызове утилиты SQL*Loader из командной строки символ обратной косой черты (\) в конце каждой строки означает, что команда продолжается на следующей строке. Задавать параметры командной строки можно, как указывая их имена, так и указывая их позиции. Например, параметр, отвечающий за имя пользователя и пароль, всегда следует за ключевым словом sqlldr. В случае пропуска параметра Oracle будет использовать принятое по умолчанию значение для этого параметра. При желании после указания каждого параметра можно добавлять символ запятой.

Как не трудно заметить, чем больше параметров требуется использовать, тем больше информации приходится предоставлять в командной строке. Такой подход имеет два недостатка. Во-первых, в случае допущения опечаток или других ошибок получается путаница. Во-вторых, в некоторых операционных системах может существовать ограничение на количество символов, которое может вводиться в командной строке. К счастью, то же самое задание может быть запущено и с помощью следующей команды, которая является куда менее сложной:

Параметр PARFILE представляет файл параметров (parameter file), т.е. файл, который может содержать значения для всех командных параметров. Например, для демонстрируемых в этой главе спецификаций загрузки этот файл выглядит так:

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

При желании применять командную строку, но исключить вероятность подглядывания кем-нибудь вводимого пароля, SQL*Loader можно вызывать следующим образом:

В таком случае SQL*Loader будет отображать приглашение на ввод имени пользователя и пароля.

Журнальный файл SQL*Loader

В журнальном файле утилиты SQL*Loader содержится масса информации касательно ее сеанса работы. В нем сообщается о том, сколько записей должно было быть загружено и сколько было загружено на самом деле, а также какие записи не удалось загрузить и почему. Кроме того, в нем описываются столбцы, которые были указаны для полей в управляющем файле SQL*Loader. В листинге 13.2 приведен пример типичного журнального файла SQL*Loader.

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

Использование кодов завершения

В журнальном файле фиксируется масса информации касательно процесса загрузки, но Oracle также позволяет перехватывать после каждого выполнения загрузки код завершения (exit code). Такой подход предоставляет возможность проверять результаты загрузки, когда та выполняется посредством задания cron или сценария оболочки. В случае использования сервера Windows для планирования выполнения заданий по загрузке можно применять команду at. Ниже перечислены ключевые коды завершения, которые могут встречаться в операционных системах UNIX и Linux:

  • EX_SUCC 0 обозначает, что все строки были загружены успешно;
  • EX_FAIL 1 обозначает, что были обнаружены какие-то ошибки в командной строке или синтаксисе;
  • EX_WARN 2 обозначает, что были отвергнуты некоторые или все строки;
  • EX_FTL 3 обозначает, что произошли какие-то ошибки в операционной системе.

Использование метода загрузки в прямом режиме

Пока что утилита SQL*Loader рассматривалась с точки зрения обычного режима загрузки. Как мы помним, метод выполнения загрузки в обычном режиме предусматривает использование SQL-операторов INSERT для вставки данных в таблицы в объеме одного массива связывания за раз. Метод выполнения загрузки в прямом режиме не предусматривает применение SQL-операторов для размещения данных в таблицах, вместо этого он подразумевает форматирование блоков данных Oracle и их запись непосредственно в файлы базы данных. Такой процесс прямой записи устраняет большинство из накладных расходов, которые имеют место при выполнении SQL-операторов для загрузки таблиц. Поскольку метод загрузки в прямом режиме не предусматривает борьбы за ресурсы базы данных, он будет работать гораздо быстрее метода загрузки в обычном режиме. Для загрузки больших объемов данных прямой режим загрузки подходит больше всего и может оказываться единственно эффективным методом по той простой причине, что выполнение загрузки в обычном режиме потребует больше времени, чем доступно.

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

  • Загрузка происходит гораздо быстрее, чем при применении метода загрузки в обычном режиме, поскольку SQL-операторы не используются.
  • Для выполнения записи данных в базу данных применяются многоблочные асинхронные операции ввода-вывода, поэтому запись происходит быстро.
  • Доступна возможность выполнения предварительной сортировки данных с помощью эффективных сортировочных подпрограмм.
  • За счет установки для параметра UNRECOVERABLE значения Y (UNRECOVERABLE=Y) может предотвращаться осуществление во время загрузки записи каких-либо данных повторного выполнения.
  • За счет применения механизма временного хранения построение индексов может производиться более эффективно, чем при использовании обычного режима загрузки.

На заметку! При обычном режиме загрузки будут всегда генерироваться записи повторного выполнения, в то время как при прямом режиме загрузки такие записи будут генерироваться только в определенных условиях. Кроме того, при прямом режиме не будут срабатывать триггеры вставки, которые в обычном режиме всегда срабатывают во время процесса загрузки. И, наконец, в отличие от обычного режима, прямой режим будет исключать возможность внесения пользователями каких-либо изменений в загружаемую данными таблицу.

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

  • при использовании кластеризованных таблиц;
  • при выполнении загрузки данных одновременно в родительские и дочерние таблицы;
  • при выполнении загрузки данных в столбцы VARRAY или BFILE;
  • при выполнении загрузки среди гетерогенных платформ с использованием Oracle Net;
  • при желании использовать во время загрузки SQL-функции.

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

Параметры, которые можно применять в случае использования метода загрузки в прямом режиме

Несколько параметров в SQL*Loader предназначены специально для использования вместе с методом загрузки в прямом режиме или больше подходят для этого метода, чем для метода загрузки в обычном режиме. Эти параметры описаны ниже.

  • DIRECT. При желании использовать метод загрузки в прямом режиме для конструкции DIRECT должно обязательно устанавливаться значение true (DIRECT=true).
  • DATA_CACHE. Параметр DATA_CACHE удобно использовать в случае неоднократной загрузки одних и тех же данных или значений даты и времени (TIMESTAMP) во время выполнения загрузки в прямом режиме. Утилите SQL*Loader приходится преобразовывать данные о дате и времени всякий раз, когда они ей встречаются. Поэтому при наличии дублированных значений даты и времени в загружаемых данных установка параметра DATA_CACHE позволит сократить количество излишних операций по преобразованию этих значений и, следовательно, уменьшить время обработки. По умолчанию параметр DATA_CACHE предусматривает сохранение в кэше 1000 значений. Если в данных нет никаких дублированных значений даты и времени или есть, но очень мало, этот параметр можно вообще отключить, установив для него значение 0 (DATA_CACHE=0).
  • ROWS. Параметр ROWS играет важную роль, поскольку позволяет указывать, сколько строк утилита SQL*Loader должна считывать из входного файла данных, прежде чем сохранять вставки в таблицах. Он применяется для определения верхнего предела объема данных, теряемых в случае возникновения сбоя в работе экземпляра во время выполнения длинного задания SQL*Loader. После считывания указанного в этом параметре количества строк SQL*Loader будет останавливать загрузку данных до тех пор, пока содержимое всех буферов данных не будет успешно записано в файлы данных. Это процесс называется сохранением данных (data save). Например, в случае наличия у SQL*Loader возможности загружать около 10 000 строк в минуту установка для параметра ROWS значения 150 000 (ROWS=150000) приведет к тому, что сохранение данных будет происходить каждые 15 минут.
  • UNRECOVERABLE. Параметр UNRECOVERABLE позволяет сводить к минимуму использование журнала повторного выполнения во время загрузки данных в прямом режиме (он задается в управляющем файле).
  • SKIP_INDEX_MAINTENANCE. Параметр SKIP_INDEX_MAINTENANCE, когда включен (SKIP_INDEX_MAINTENANCE=true), указывает SQL*Loader не беспокоиться об обслуживании индексов во время загрузки. По умолчанию для него устанавливается значение false.
  • SKIP_UNUSABLE_INDEXES. Установка значения true для параметра SKIP_UNUSABLE_INDEXES будет гарантировать выполнение утилитой SQL*Loader загрузки даже таблиц, индексы которых находятся в непригодном для использования состоянии. Обслуживаться SQL*Loader, однако, эти индексы не будут. Значение, устанавливаемое для этого параметра по умолчанию, зависит от того, какое значение выбрано для параметра инициализации SKIP_UNUSABLE_INDEXES, для которого по умолчанию устанавливается true.
  • SORTED_INDEXES. Параметр SORTED_INDEXES уведомляет SQL*Loader о выполнении сортировки данных на уровне определенных индексов, что помогает ускорять процесс загрузки.
  • COLUMNARRAYROWS. Этот параметр позволяет указывать, сколько строк должно загружаться перед построением буфера потоков. Например, в случае установки для него значения 100 000 (COLUMNARRAYROWS=100000), сначала будет выполняться загрузка 100 000 строк. Следовательно, получается, что размер массива столбцов во время выполнения загрузки в прямом режиме будет зависеть от значения этого параметра. У автора на сервере UNIX значение этого параметра по умолчанию составляло 5000 строк.
  • STREAMSIZE. Параметр STREAMSIZE позволяет задавать размер буфера потока. У автора на сервере UNIX, например, этот размер по умолчанию составлял 256 000 строк; при желании увеличить его, можно было бы установить параметр STREAMSIZE, например, так: STREAMSIZE=51200.
  • MULTITHREADING. При включении параметра MULTITHREADING операции преобразования массивов столбцов в буферы потоков и затем загрузки этих буферов потоков выполняются параллельно. На машинах с несколькими ЦП этот параметр по умолчанию включен (имеет значение true). При желании его можно отключить, установив в false (MULTITHREADING =false).

Управление ограничениями и триггерами при использовании метода загрузки в прямом режиме

Метод загрузки в прямом режиме подразумевает осуществление вставки данных прямо в файлы данных за счет форматирования блоков данных. Поскольку операторы INSERT не используются, никакого системного применения ограничений и триггеров таблиц при прямом режиме загрузки не происходит. Вместо этого все триггеры, равно как и некоторые ограничения целостности, отключаются. Утилита SQL*Loader автоматически отключает все внешние ключи и проверочные ограничения целостности, но ненулевые, уникальные и связанные с первичными ключами все равно поддерживает. По завершении выполнения задания SQL*Loader снова автоматически включает все отключенные ограничения, если была указана конструкция REENABLE. В противном случае их потребуется включить вручную. Что касается триггеров, то они всегда автоматически включаются снова после окончания процесса загрузки.

Советы по оптимальному использованию SQL*Loader

Чтобы использовать SQL*Loader оптимальным образом, особенно при выполнении загрузки данных большого объема и/или наличии множества индексов и ограничений, ассоциируемых с таблицами в базе данных, рекомендуется поступать следующим образом.

  • Стараться как можно чаще применять метод загрузки в прямом режиме. Он работает гораздо быстрее метода загрузки в обычном режиме.
  • Использовать везде, где возможно (при прямом режиме загрузке), опцию UNRECOVERABLE= true. Это позволит экономить приличное количество времени, потому что новые загружаемые данные не нужно будет фиксировать в файле журнала повторного выполнения. Возможность выполнения восстановления носителя при этом все равно остается в силе для всех остальных пользователей базы данных, плюс в случае возникновения проблемы всегда можно запустить новый сеанс SQL*Loader.
  • Сводить использование параметров NULLIF и DEFAULTIF к минимуму. Эти конструкции должны обязательно подвергаться тестированию в случае каждой строки, для которой они применяются.
  • Ограничивать количество операций по преобразованию типов данных и наборов символов, потому что они замедляют процесс обработки.
  • Везде, где возможно, использовать для полей позиции, а не разделители. У утилиты SQL*Loader получается гораздо быстрее переходить от поля к полю, когда ей предоставляются их позиции.
  • Отображать физические и логические записи по схеме “один к одному”.
  • Отключать ограничения перед началом процесса загрузки, потому что они будут замедлять его. Конечно, при включении ограничений снова иногда могут появляться ошибки, но гораздо более быстрое выполнение загрузки данных того стоит, особенно в случае крупных таблиц.
  • Указывать в случае использования метода загрузки в прямом режиме конструкцию SORTED_INDEXES для оптимизации скорости выполнения загрузки.
  • Удалять ассоциируемые с таблицами индексы перед началом процесса загрузки в случае выполнения загрузки больших объемов данных. При невозможности удалить индексы можно делать их непригодными для использования и применять во время загрузки конструкцию SKIP_UNUSABLE_INDEXES, а в случае выполнения загрузки в прямом режиме — еще и конструкцию SKIP_INDEX_MAINTENANCE.

Некоторые полезные приемы для загрузки данных с помощью SQL*Loader

Применение SQL*Loader является эффективным подходом, но не без своей доли хитростей. В этом разделе рассказывается о том, как выполнять во время загрузки данных некоторые особые виды операций.

Использование конструкции WHEN во время операций загрузки данных

Конструкцию WHEN можно использовать во время операций загрузки данных для ограничения загружаемых данных только теми строками, которые отвечают определенным условиям. Например, с ее помощью можно делать так, чтобы из файла данных выбирались только те записи, в которых присутствует поле, удовлетворяющее конкретным критериям. Ниже приведен пример, демонстрирующий применение конструкции WHEN в управляющем файле SQL*Loader:

Здесь условие в конструкции WHEN указывает, что все записи, в которых поле, соответствующее столбцу activity_type в таблице stagetbl, не содержит ни H, ни T, должны отклоняться.

Загрузка имени пользователя в таблицу

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

Загрузка больших полей данных в таблицу

При попытке загрузить в таблицу любое поле размером более 255 байт, даже в случае присвоения столбцу типа VARCHAR(2000) или CLOB, утилите SQL*Loader не удастся произвести загрузку данных, и потому она будет выдавать сообщение об ошибке Field in datafile exceeds maximum length (Поле в файле данных превышает максимально допустимую длину). Для загрузки большого поля необходимо обязательно указывать в управляющем файле размер соответствующего ему столбца в таблице при отображении столбцов таблицы на поля данных, как показано в следующем примере (где соответствующий столбец имеет имя text):

Загрузка номера последовательности в таблицу

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

Загрузка данных из таблицы в файл ASCII

Иногда бывает необходимо извлекать данные из таблицы базы данных в плоские файлы, например, с целью последующего их использования для загрузки данных в таблицы Oracle, находящиеся в другом месте. При наличии множества таких таблиц можно написать сложные сценарии, но если речь идет о всего лишь нескольких таблицах, то вполне подойдет и следующий простой метод извлечения данных с помощью команд SQL*Plus:

Также для загрузки данных в текстовые файлы можно применять пакет UTL_FILE.

Удаление индексов перед загрузкой больших массивов данных

Есть две основных причины, по которым следует всерьез задумываться об удалении индексов, ассоциируемых с большой таблицей, перед выполнением загрузки данных в прямом режиме с использованием опции NOLOGGING. Во-первых, загрузка вместе с поставляемыми с табличными данными индексами может занимать больше времени. Во-вторых, в случае оставления индексов активными, внесение изменений в их структуру во время загрузки будет приводить к генерации записей повторного выполнения (redo records).

Совет. При выборе варианта выполнения загрузки данных с использованием опции NOLOGGING будет генерироваться приличное количество данных повторного выполнения для обозначения вносимых в индексы изменений. Помимо этого еще кое-какие данные повторного выполнения будут генерироваться и для поддержки словаря данных, даже во время выполнения самой операции загрузки данных с опцией NOLOGGING. Поэтому наилучшей стратегией в таком случае является удаление индексов и их повторное построение после создания таблиц.

При выполнении загрузки в прямом режиме где-то на полпути может происходить сбой в работе экземпляра, заканчиваться пространство, необходимое утилите SQL*Loader для осуществления обновления индекса, или встречаться дублированные значения ключей индексов. Все подобные ситуации называются условием приведения индексов в непригодное состояние, потому что после восстановления экземпляра индексы становятся непригодными для использования. Во избежание этих ситуаций тоже может быть лучше создавать индексы после завершения процесса загрузки.

Выполнение загрузки данных в несколько таблиц

Для выполнения загрузки данных в несколько таблиц можно применять все ту же утилиту SQL*Loader. Ниже приведен пример выполнения загрузки данных сразу в две таблицы:

В этом примере данные из одного и того же файла данных загружаются одновременно в две таблицы — dept и emp — на основании того, содержится в поле recid значение 1 или нет.

Перехват кодов ошибок в SQL*Loader

Ниже представлен простой пример того, как можно перехватывать выдаваемые SQL*Loader коды ошибок:

Загрузка XML-данных в XML-базу данных Oracle

Утилита SQL*Loader поддерживает использование для столбцов типа данных XML. При наличии такого столбца, она, соответственно, может применяться для загрузки в таблицу XML-данных. Утилита SQL*Loader воспринимает XML-столбцы как столбцы типа CLOB (Character Large Object — большой символьный объект). Вдобавок Oracle позволяет загружать XML-данные как из первичного файла данных, так и из внешнего файла формата LOB (Large Object — большой объект), и использовать как поля фиксированной длины, так и поля с разделителями, а также считывать все содержимое файла в одно единственного поле типа LOB.

SQL field type field not shows up

I have a MySQL table in which one of the fields is variation and type is float . Its value is 0.2 . But when I query like WHERE variation = ‘0.2’ it is not showing the result, instead it returns null. What I am doing wrong?

4 Answers 4

Remove the quotes around the number a try again.

eg WHERE variation = 0.2

Edit: As Peeka pointed out, the quotes don’t matter in MySQL.

Are you absolutely sure the value of the float is exactly 0.2 and there aren’t significant digits that are getting rounded? Can you try using BETWEEN 0.19 AND 0.21 and seeing whether the row you’re after gets returned (I know it’s not an equivalent query and may return unwanted results but it will test whether the value is exactly 0.2 or whether it’s rounded in the client).

Edit 2: I’d wager it’s because of the way MySQL stores floats — taken from their manual:

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally.

It is recommended to use a range to filter instead of an equality, so if you use BETWEEN 0.19 AND 0.21 do you get the result you want?

Что такое код fbsql_field_type

В следующем примере требуется ссылка COM на Microsoft ADO Ext. 2.8 для DDL и безопасности:

Хотя Microsoft усложняет дело, имея поле гиперссылки в базе данных как объект со свойствами, настройка кажется довольно простой, используя такой синтаксис:

UPDATE myTable SET HyperlinkField = «(текст для отображения) # (целевой URL) # (подполе гиперссылки, если требуется)» Предложение WHERE

Фактически, я использую это для создания формы над таблицей, где ссылка указывает на файл, а не на веб-адрес (это список чертежей, чтобы пользователь мог щелкнуть ссылку, чтобы открыть чертеж). В этом случае URL-адрес – это путь RELATIVE к файлу, например «.. \ documents \ myFile1.dat».

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

Я не тестировал, как эти ссылки ведут себя, когда исходная база данных перемещается, кстати.

Вы должны использовать два поля CHARACTER для представления содержимого гиперссылки, которое содержит URL-адрес ресурса, на который вы хотите связать гиперссылку (сохраненную как обычный текст), и другую, которая будет содержать текст, отображаемый в браузере (это если текст, отображаемый в браузере, отличается от самого URL).

При отображении ссылки на веб-странице на C # вы можете создать гиперссылку:

Следуя инструкциям ворона, фиктивные программисты (например, я) могут использовать этот sub, чтобы добавить, используя код VBA, поле гиперссылки.

Соответствующий SQL для вставки новой записи со ссылкой на (например) c: \ is ..

SQL form field type

Материал из Joomla! Documentation

Using this generic form field type forces you to write SQL in an XML file and is rather limited. For more flexibility, consider creating your own, specific form field type by subclassing the JFormField class.

The sql form field type provides a drop down list of entries obtained by running a query on the Joomla database. If the field has a value saved, this value is selected when the page is first loaded. If not, the default value (if any) is selected.

  • type (mandatory) must be sql.
  • name (mandatory) is the unique name of the field. This must match the name of the query results column that contains the values that will be shown to the user in the drop-down list, unless a different name is specified in the value_field attribute.
  • label (mandatory) (translatable) is the descriptive title of the field.
  • query (mandatory if not using the sql_* attributes) is the SQL query which will prov entry to the list. See the examples for an alternative way of achieving this.
  • sql_select (mandatory if not using the query attribute) is the SELECT clause of the SQL statement. Only one such clause is permitted.
  • sql_from (mandatory if not using the query attribute) is the FROM clause of the SQL statement.
  • sql_join (optional) is the LEFT JOIN clause of the SQL statement. Only one such clause is permitted.
  • sql_where (optional) is the WHERE clause of the SQL statement. Only one such clause is permitted.
  • sql_group (optional) is the GROUP BY clause of the SQL statement.
  • sql_order (optional) is the ORDER BY clause of the SQL statement.
  • sql_filter (optional) filters the list by the value of another field. A field name or a comma-separated list of field names can be given. The field names must correspond to column names in the database table being queried. See the examples for further explanation.
  • sql_default_ (optional) is the default value used by the sql_filter attribute when the value of the filter has not been set. See the examples for further explanation.

Example XML parameter definition:

Notice that an AS clause has been used in this example because the jos_content table does not have a column called ‘value’. In fact very few tables in the Joomla database have a column called ‘value’. Alternatively, you can use a key_field attribute to define the column to be used instead of ‘value’:

This will give identical results to the previous example.

Both column names may need to be aliased. For example, suppose you want your field to be called ‘myfield’ instead of ‘title’ in the previous example. Then you can do this:

You can also assemble or calculate fields in the SQL statement. For example, suppose you wanted to append the created date/time of each article to the article title in the list. Then you could use this SQL statement:

You can also specify a static option in the XML using tag. Please look at the following example.

Alternatively, you can achieve the same result using the header attribute as follows:

Alternative query syntax

Starting with Joomla 3.5, an alternative to the query attribute allows some additional features. These features are not available if the query attribute is present. For example, this field definition:

can be expressed as:

The following feature linked fields as filters is currently not working! See Github issue 22241

One advantage to using this syntax is that it allows the use of linked fields as filters. For example, suppose you have a form containing two select lists, one called groups and the other called subgroups. The groups field is straightforward:

but the subgroups field includes an sql_filter attribute which refers to the groups field by name:

Then if the groups field has the value 99, the following SQL statement will be executed for the subgroups field:

To filter on multiple fields, you can use a comma-separated list of filter names in the sql_filter clause. For example, if there is a filter called groups with the value 99 and a filter called categories with the value 12, then

will produce the SQL WHERE clause:

You can also define a default value for any filter that might not have a value when the field is evaluated by adding sql_default_ attributes. For example, suppose that the default value for the groups filter is 0 and the default value for the categories filter is 0, then this definition:

will produce this SQL statement when initially evaluated with no filters:

Note: The SQL statements will need to be correct for the type and version of the underlying database that Joomla is running on. This will most likely be a version of MySQL, but it could be something else. There is no capability to query databases other than the one Joomla itself is running on.

Note: As shown in these examples, the database prefix (often jos ) should be entered in the form #__ (hash-underscore-underscore). It will automatically be replaced by the actual database prefix used by Joomla.

Standard SQL в Google BigQuery: преимущества и примеры использования в маркетинге

Получайте новые статьи на эл. почту

В 2020 году в Google BigQuery появился новый способ общения с таблицами — Standard SQL. До этого времени у сервиса была собственная версия языка структурированных запросов — BigQuery SQL, которая сейчас называется Legacy.

На первый взгляд, между Legacy и Standard SQL нет большой разницы: немного по-другому пишутся названия таблиц, у стандарта чуть жестче требования к грамматике (например, нельзя ставить запятую перед FROM) и больше типов данных. Но если присмотреться, за небольшими отличиями стоят изменения синтаксиса, которые дают маркетологам много преимуществ.

В этой статье вы узнаете:

В чем преимущества Standard SQL перед Legacy SQL

Новые типы данных — массивы и вложенные поля

Стандартный SQL поддерживает новые типы данных — ARRAY и STRUCT (массивы и вложенные поля). Это означает, что в BigQuery стало проще работать с таблицами, загружаемыми из файлов JSON/Avro, данные в которых часто содержат многоуровневые вложения.

Вложенное поле — это мини-таблица внутри большой:

На приведенной схеме голубые и розовые полосы — это строки, в которые вложены мини-таблички. Каждая строка — это одна сессия. У сессии есть общие параметры: дата, уникальный номер, категория устройства пользователя, браузер, операционная система и т.д. Кроме общих параметров для каждой сессии, в строку вложена табличка hits.

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

Если пользователь в течение визита сделал заказ на сайте, в поле hits запишутся характеристики заказа:

  • transactionId — номер.
  • transactionRevenue — доход.
  • transactionShipping — стоимость доставки и др.

Допустим, вы хотите узнать количество заказов от пользователей из Нью-Йорка за последний месяц. Для этого вам нужно посчитать количество уникальных transactionId, обратившись к полю hits. Чтобы добыть данные из таких полей, в Standard SQL есть функция UNNEST:

Если бы параметры заказов записывались не во вложенное поле, а в отдельную табличку, вам пришлось бы с помощью JOIN объединить таблицы о заказах с данными о сессиях, чтобы узнать, в каких сессиях были сделаны заказы.

Больше вариантов подзапросов

Если у вас многоуровневые вложенные поля, чтобы извлечь из них данные, вы можете добавлять подзапросы в SELECT и WHERE. К примеру, в таблицах сессионного стриминга OWOX BI в подтаблицу hits записывается еще одна подтаблица — product. В ней собираются данные о продукте, которые передаются с массивом Enhanced Ecommerce. Если на сайте настроена расширенная электронная торговля и пользователь посмотрел карточку товара, в подтаблицу product запишутся характеристики этого товара.

Чтобы получить характеристики товара, вам понадобится подзапрос внутри основного запроса. Для каждой характеристики товара создается отдельный подзапрос SELECT в круглых скобках:

Благодаря возможностям стандартного SQL проще выстроить логику запроса и написать код. Для сравнения — на Legacy SQL можно писать только вот такую «лесенку»:

Запросы к внешним источникам

С помощью Standard SQL можно обращаться из BigQuery напрямую к таблицам Google Bigtable, Google Cloud Storage, Google Drive и Google Sheets.

То есть вместо того, чтобы загружать в BigQuery таблицу целиком, вы можете одним единственным запросом очистить данные, выбрать те параметры, которые вам нужны, и загрузить их в облачное хранилище.

Больше пользовательских функций (UDF)

Если вам нужно использовать формулу, которой нет в документации, вам помогут пользовательские функции (User Defined Functions). В нашей практике это очень редкий случай, поскольку документация Standard SQL покрывает почти все задачи digital-аналитики.

В стандартном диалекте пользовательские функции можно писать на SQL или JavaScript, а в Legacy поддерживается только JavaScript. В качестве аргументов функции используются колонки, а значения, которые она принимает — это результаты действий с колонками. На стандартном диалекте функции можно писать в том же окошке, что и запросы.

Больше условий в JOIN

В Legacy SQL в качестве условий JOIN можно задавать равенство или название колонок. Стандартный диалект поддерживает также JOIN по неравенству и по произвольному выражению.

Например, для выявления недобросовестных CPA-партнеров мы выбираем сессии, в которых была подмена источника в течение 60 секунд перед транзакцией. Для этого на стандартном диалекте можно добавить неравенство в условие JOIN:

Единственное ограничение стандартного диалекта в отношении JOIN в том, что он не разрешает «половинчатые джойны» (semi-join) с подзапросами вида WHERE column IN (SELECT. ):

Меньше шансов ошибиться

Некоторые функции в Legacy SQL возвращают NULL, если условие задано неверно. Например, если в ваши вычисления закралось деление на ноль, запрос выполнится, а в результирующих строках таблицы появятся записи NULL. Это может маскировать проблемы в запросе или в данных.

Логика стандартного SQL более прямолинейна. Если условие или входящие данные неверны, запрос выдаст ошибку, например «division by zero», и вы сможете быстро поправить текст запроса. В стандарт SQL встроены:

  • Проверка допустимых значений для +, −, ×, SUM, AVG, STDEV.
  • Проверка деления на ноль.

Запросы выполняются быстрее

Запросы с JOIN, написанные на стандартном SQL, выполняются быстрее, чем написанные на Legacy, благодаря предварительной фильтрации входящих данных. Сначала запрос выбирает строки, которые соответствуют условиям JOIN, а затем обрабатывает их.

В дальнейшем Google BigQuery будет работать над повышением скорости и производительности запросов только для Standard SQL.

Таблицы можно редактировать: вставлять и удалять строки, обновлять

Для стандартного диалекта доступны функции Data Manipulation Language (DML). Это означает, что обновлять таблицы, добавлять или удалять из них строки можно через то же окно, через которое вы пишете запросы. Например, с помощью DML можно объединить данные двух таблиц в одну так, чтобы они дополняли друг друга:

Код удобнее читать и править

Теперь сложные запросы можно начинать не только с SELECT, но и с WITH. Такой код проще читать, комментировать и понимать, что хотел сказать автор. А значит, проще предотвратить собственные и исправить чужие ошибки.

С оператором WITH удобно работать, если у вас есть вычисления, которые делаются в несколько этапов. Сначала вы собираете в подзапросах промежуточные метрики, а затем делаете финальные расчеты.

Google Cloud Platform (GCP), в которую входит BigQuery — это платформа полного цикла работы с большими данными, от организации Data Warehouse или Data Cloud до научных экспериментов, предиктивной и прескриптивной аналитики. За счет внедрения стандартного SQL BigQuery расширяет свою аудиторию. Работать с GCP становится интереснее маркетинг-аналитикам, продуктовым аналитикам, Data Scientists и другим командам.

Возможности и примеры использования Standard SQL

Мы в OWOX BI часто работаем с таблицами, собранными при помощи стандартного экспорта Google Analytics 360 в Google BigQuery или через OWOX BI Pipeline. Поэтому в примерах ниже мы рассмотрим особенности SQL-запросов именно к таким данным.

Если вы еще не собираете данные с сайта в BigQuery, вы можете попробовать это бесплатно в trial-версии от OWOX BI.

1. Выбрать данные за интервал времени

В Google BigQuery данные о поведении пользователей на сайте хранятся в wildcard tables (таблицах со звездочкой): за каждый день формируется отдельная таблица. Таблицы по дням называются одинаково — отличается только суффикс в имени. В суффикс записывается дата в формате ГГГГММДД. Например, в таблице owoxbi_sessions_20200301 лежат данные о сессиях за 1 марта 2020 года.

Мы можем в одном запросе обратиться сразу к группе таких таблиц, чтобы получить данные, к примеру, с 1 по 28 февраля 2020 года. Для этого в FROM нужно указать вместо ГГГГММДД значок *, а в WHERE — назвать, какие табличные суффиксы должны войти в интервал времени:

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

После BETWEEN записан первый табличный суффикс. Формулировка CURRENT_DATE(), INTERVAL 3 MONTHS означает «выбери данные за последние 3 месяца от текущей даты». Второй табличный суффикс записан после AND. Он нужен, чтобы обозначить окончание интервала — вчерашний день: CURRENT_DATE(), INTERVAL 1 DAY.

2. Извлечь пользовательские параметры и показатели

Пользовательские параметры и показатели в таблицах Google Analytics Export записываются во вложенное поле hits, в подтаблицы сustomDimensions и customMetrics. Все custom dimensions записываются в две колонки: в одной — номера параметров, которые собираются на сайте, во второй — их значения. Вот как выглядят все параметры, которые передались с одним хитом:

Чтобы их «распаковать» и записать нужные параметры в отдельные колонки, мы пользуемся следующими формулировками в SQL-запросе:

Вот как это выглядит в запросе:

На скриншоте ниже мы выбрали параметры № 1 и № 2 из демо-данных экспорта Google Analytics 360 в Google BigQuery, назвали их page_type и client_id. Каждый параметр записался в отдельную колонку:

3. Посчитать количество сессий в разрезе источника трафика, канала, кампании, города и категории устройства

Такие расчеты полезны, если вы планируете визуализировать данные в Google Data Studio и пользоваться фильтрами по городам и категориям устройств. Это очень просто сделать с оконной функцией COUNT:

4. Объединить одинаковые данные из нескольких таблиц

Предположим, вы собираете данные о выполненных заказах в нескольких таблицах BigQuery: в одной собираются все заказы из магазина А, в другой — из магазина B. Вы хотите сделать из них одну таблицу с такими колонками:

  • client_id — идентификатор покупателя, который сделал заказ.
  • transaction_created — время создания заказа в формате TIMESTAMP.
  • transaction_id — номер заказа.
  • is_approved — подтвержден ли заказ.
  • transaction_revenue — сумма заказа.

В таблицу должны попасть заказы с 1 января 2020 по вчерашний день. Для этого из каждой группы таблиц нужно выбрать подходящие колонки, присвоить им одинаковые имена и объединить результаты с помощью UNION ALL:

5. Создать словарь групп каналов трафика

Когда данные попадают в Google Analytics, система автоматически определяет группу, к которой относится тот или иной переход: Direct (прямой трафик), Organic Search, Paid Search и так далее. Для определения группы каналов Google Analytics «смотрит» на utm-метки переходов, а именно utm_source и utm_medium. Подробнее о группах каналов и о правилах определения, можно почитать в справке Google Analytics.

Если клиенты OWOX BI хотят присвоить собственные названия группам каналов, мы создаем для него словарь, куда какой переход относится. Для этого используем условный оператор CASE и функцию REGEXP_CONTAINS. Эта функция выбирает значения, в которых встречается заданное регулярное выражение.

Пример, как добавить такие условия в тело запроса (названия рекомендуем брать из вашего списка источников в GA):

Как перейти на Standard SQL

Если вы еще не перешли на стандартный SQL, то можете сделать это в любой момент. Главное — не смешивать диалекты в одном запросе.

Вариант 1. Переключатель в интерфейсе Google BigQuery

В старом интерфейсе BigQuery по умолчанию используется Legacy SQL. Для переключения между диалектами нажмите «Show Options» под полем ввода запроса и снимите галочку «Use Legacy SQL» возле пункта «SQL Dialect»:

В новом интерфейсе по умолчанию используется Standard SQL. Здесь переключение диалектов находится во вкладке «More»:

Вариант 2. Написать префикс в начале запроса

Если вы не поставили галочку в настройках запроса, можно начать его с нужного префикса (#standardSQL или #legacySQL):

В этом случае Google BigQuery проигнорирует настройки в интерфейсе и запустит запрос так, как вы написали в префиксе.

Если у вас есть представления или сохраненные запросы, которые запускаются с помощью Apps Script по расписанию, не забудьте поменять в скрипте значение useLegacySql на false:

Вариант 3. Переход на Standard SQL для представлений (Views)

Если вы работаете в Google BigQuery не с таблицами, а с представлениями, к ним нельзя обращаться на разных диалектах. То есть, если ваше представление написано на Legacy SQL, к нему нельзя писать запросы на Standard.

Чтобы перевести представление на стандартный SQL, нужно вручную переписать запрос, которым оно создано. Проще всего это сделать через интерфейс BigQuery.

1. Откройте нужное представление:

2. Нажмите «Details». Когда откроется текст запроса, внизу появится кнопка «Edit Query»:

Теперь запрос можно редактировать по правилам стандартного диалекта. Если вы планируете дальше пользоваться им как представлением, после того, как закончите редактировать, нажмите «Save View».

Совместимость, особенности синтаксиса, операторы, функции

Совместимость

Благодаря внедрению стандартного SQL, непосредственно из BigQuery можно обращаться к данным, которые хранятся в других сервисах:

  • Файлам логов в Google Cloud Storage.
  • Транзакционным записям в Google Bigtable.
  • Другим источникам.

Это позволяет использовать продукты Google Cloud Platform для любых аналитических задач, в том числе для предиктивной и прескриптивной аналитики на базе алгоритмов машинного обучения.

Синтаксис запроса

Структура запроса в стандартном диалекте — почти такая же, как и в Legacy:

Названия таблиц и View пишутся через точку и отбиваются обратным машинописным апострофом: `название_проекта.название_набора данных.название_таблицы`. Например: `bigquery-public-data.samples.natality`.

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

Особенности синтаксиса стандартного SQL:

  • Запятые нужны для перечисления полей в операторе SELECT.
  • Если вы используете оператор UNNEST после FROM , перед UNNEST ставится запятая или JOIN.
  • Нельзя ставить запятую перед FROM.
  • Запятая между двумя запросами приравнивается к CROSS JOIN, поэтому будьте с ней осторожны.
  • JOIN можно делать не только по колонкам или равенствам, но и по произвольным выражениям и неравенствам.
  • Можно писать сложные подзапросы в любой части SQL-выражения (в SELECT, FROM, WHERE). На практике пока что нельзя использовать выражения вида WHERE column_name IN (SELECT. ), как это работает в других базах данных.

Операторы

В стандартном SQL операторы определяют тип данных. Например, массив всегда записывается в стандартных скобках [ ]. Операторы используются для сравнения, поиска соответствий логическому выражению (NOT, OR, AND) и арифметических вычислений.

Функции

Standard SQL поддерживает больше функций, чем Legacy — от традиционных функций агрегирования (сумма, количество, минимум, максимум), математических, строковых и статистических функций до редких форматов — например, HyperLogLog++.

В стандартном диалекте больше функций для работы с датами и TIMESTAMP. Полный список функций приведен в справке Google. Чаще всего используются функции для работы с датами, строками, функции агрегирования и оконные.

1. Функции агрегирования

COUNT(DISTINCT column_name) считает количество уникальных значений в столбце. Например, нам нужно посчитать количество сессий, сделанных с мобильных устройств, за 1 марта 2020 года. Поскольку номер сессии может повторяться в разных строках, мы хотим посчитать только уникальные значения номера сессии:

SUM (column_name) — сумма значений в колонке:

MIN (column_name) | MAX (column_name) — минимальное и максимальное значение в колонке. Эти функции очень удобны для проверки разброса данных в таблице.

2. Оконные (аналитические) функции

Аналитические функции считают значения не по всей таблице, а по определенному «окну» — набору строк, который вам интересен. То есть, сделать внутри таблицы сегменты. Например, вы можете посчитать доход SUM (Revenue) не по всем строкам, а по городам, категориям устройств и так далее. Вы можете превратить в аналитическую функцию SUM, COUNT, AVG и другие функции агрегирования, если добавите к ним условие OVER (PARTITION BY column_name).

К примеру, вам нужно посчитать количество сессий в разрезе источников трафика, каналов и кампаний, городов и категорий устройств. В таком случае используйте выражение:

OVER определяет окно, в котором будут производиться вычисления. PARTITION BY указывает, какие строки нужно сгруппировать для расчета. В некоторых функциях обязательно нужно указывать порядок группировки — ORDER BY.

Полный список оконных функций вы найдете в документации BigQuery.

3. Функции работы со строками

Они полезны, когда вам нужно изменить, отформатировать текст в строке, склеить значения колонок. Например, если вы хотите сформировать уникальный идентификатор сессии из данных стандартного экспорта Google Analytics 360. Рассмотрим самые популярные из них.

SUBSTR вырезает часть строки. В запросе эта функция записывается так: SUBSTR(string_name, 0,4). Первое число обозначает, сколько символов нужно пропустить с начала строки, а второе — сколько цифр вырезать. Например, у вас есть столбец date, куда записываются даты в формате STRING. При этом даты выглядят так: 20200103. Если вы хотите извлечь из этой строки год, вам поможет SUBSTR:

CONCAT (column_name, etc.) — склейка значений. Воспользуемся столбцом date из предыдущего примера. Предположим, вы хотите, чтобы все даты записывались так: 2020-03-01. Для этого используются две строковые функции: сначала вы вырезаете нужные куски строки с помощью SUBSTR, а затем склеиваете их через черточку:

REGEXP_CONTAINS возвращает те значения столбцов, в которых встречается регулярное выражение:

Эту функцию можно использовать и в SELECT, и в WHERE. Например, в WHERE по ней можно выбрать конкретные страницы:

4. Функции работы с датами

Часто даты в таблицах записываются в формате STRING (строка). Если вы планируете визуализировать результаты в Google Data Studio, даты в таблице нужно перевести в формат DATE c помощью функции PARSE_DATE.

PARSE_DATE преобразует строку (STRING) вида 1900-01-01 в формат даты (DATE).
Если в ваших таблицах даты выглядят по-другому (например, 19000101 или 01_01_1900), сначала нужно привести их к указанному виду.

DATE_DIFF вычисляет, сколько времени прошло между двумя датами в днях, неделях, месяцах или годах. Пригодится, если нужно определить промежуток между тем, когда пользователь перешел по рекламе и сделал заказ. Вот как функция выглядит в запросе:

SQL-запросы для маркетинговых отчетов

Стандартный диалект позволяет бизнесу извлечь максимум информации из данных — от глубокой сегментации, технического аудита, анализа KPI маркетинга до выявления недобросовестных подрядчиков в CPA-сетях. Вот примеры бизнес-задач, в которых вам помогут SQL-запросы к данным, собранным в Google BigQuery.

1. ROPO-анализ: оцените вклад онлайн-кампаний в офлайн-продажи. Для этого нужно объединить данные о поведении пользователей в онлайне с данными из CRM, системы колл-трекинга, мобильного приложения.

Если в одной и второй базе есть ключ — общий параметр, который уникален для каждого пользователя (например, User ID), вы можете:

  • Проследить, какие пользователи заходили на сайт перед тем, как купить товар в магазине.
  • Как они вели себя на сайте.
  • Как долго принимали решение о покупке.
  • Какие кампании дают наибольший прирост офлайн-покупок.

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

Мы подготовили подборку запросов на стандартном диалекте SQL. Если вы уже собираете в Google BigQuery данные с вашего сайта, из рекламных источников и CRM-системы, то сможете использовать эти шаблоны для решения своих бизнес-задач. Просто замените в запросе название проекта, набора данных и таблицы в BigQuery на свои. В подборке вы получите 11 SQL-запросов.

Запросы к данным, собранным с помощью стандартного экспорта из Google Analytics 360 в Google BigQuery:

  • Действия пользователей в разрезе любых параметров.
  • Статистика по ключевым действиям пользователей.
  • Выбор пользователей, которые просмотрели определенные страницы товаров.
  • Действия пользователей, которые купили определенный товар.
  • Настройка воронки с любыми необходимыми шагами.
  • Эффективность работы внутреннего поиска на сайте.

Запросы к данным, собранным в Google BigQuery с помощью OWOX BI:

  • Как изменялся атрибутированный расход по источникам и каналам.
  • Как средняя стоимость привлечения посетителя зависела от города.
  • Как ROAS по валовой прибыли зависел от источника и канала.
  • Как количество заказов в CRM зависело от способа оплаты и способа доставки.
  • Как среднее время доставки зависело от города.

Если у вас есть вопросы к данным Google BigQuery, ответов на которые вы не нашли в наших кейсах, напишите об этом в комментариях. Мы постараемся вам помочь.

Тип поля формы Joomla sql = «. WHERE condition»?

В Joomla 2.5, как я могу использовать тип поля формы «SQL» с условием «WHERE» в SQL-запросе?

В документах joomla ничего нет. Я нашел эту ссылку, но не ссылается на использование условия WHERE в запросе SQL (http://docs.joomla.org/SQL_form_field_type).

Может кто-нибудь скажет мне, возможно ли это или не делать в Joomla?

У меня есть таблица с продуктами и таблица с магазинами, связанными с брендами. Если я отредактирую один магазин, я бы хотел показать combobox со всеми продуктами от бренда магазина. Это возможно? SELECT * FROM #__products WHERE brand = <идентификатор бренда, связанный с магазином, который я выбрал для редактирования>Возможно ли это?

Я смог сделать это с помощью настраиваемого поля формы =)

Илон Маск рекомендует:  Что такое код com_propput
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL