volatile table teradata что это

Импорт во временные таблицы в Teradata и экспорт результатов запроса

Зачастую, в крупных компаниях аудиторам доступ к промышленным базам данных дают с правами «только на чтение». Соответственно, при этом отсутствует возможность создавать, подгружать и выгружать свои промежуточные таблицы. Необходимость в этом может возникнуть в случае, когда, например, вы работаете с разрозненными данными, взятыми из разных источников (с разных серверов БД, расположенных в разных сетях, или из файлов разного типа и происхождения (xlsx, csv, html и т.п.), которые нужно обрабатывать совместно с данными, хранящимися на сервере Teradata.

В этом случае в Teradata на время сеанса работы с сервером можно создать свою временную область с нужными таблицами и импортировать в них данные из текстовых файлов. А кроме этого, также можно сделать обратную операцию — экспорт результатов SQL-запроса в текстовый файл. В отличие от других СУБД, которым необходимы дополнительные внешние инструменты для импорта данных из файлов, и которые не могут делать этот импорт во временные таблицы, Teradata позволяет выполнять все эти действия прямо в текущем сеансе работы, в Teradata SQL Assistant.

Рассмотрим необходимую для этого последовательность действий.

1. В Teradata SQL Assistant подключитесь к БД и активируйте режим импорта данных в пункте меню File – Import Data:

2. В пункте меню Tools – Options установите формат загружаемых текстовых данных:

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

3. Для загрузки создадим демонстрационный файл данных test_table.txt, содержащий строки:

4. Создадим и выполним новый SQL-запрос:

В результате во временной области текущего сеанса пользователя будет создана, состоящая из трёх полей ID, NAME и SALARY пустая volatile-таблица TEST_TABLE, которая будет существовать как полноценная таблица во время всей пользовательской сессии.

5. В окне SQL-запроса введите команду

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

Выберите файл и дождитесь окончания его импорта.

ВАЖНО! Скорость загрузки файла данным способом не велика и может занять продолжительное время, поэтому он применим к наборам данным не очень большого размера (тысячи, возможно — несколько десятков тысяч строк). Для быстрой загрузки в Teradata есть инструменты FastLoad, OleLoad и MultiLoad, но они, к сожалению, не работают с временными таблицами.

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

6. Проверим, правильно ли были импортированы записи из файла. Отключим режим импорта в пункте меню File – Import Data и введём запрос:

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

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

Для того, чтобы выполнить экспорт, сначала активируйте режим экспорта результатов в пункте меню File – Export Results:

8. Затем снова выполните запрос на выгрузку данных. После нажатия на кнопку «выполнить» появится запрос на экспорт данных в файл. Введите имя файла экспорта и сохраните его:

9. После выполнения запроса данные на экран выведены не будут, но в нижнем левом углу программы появится сообщение о том, сколько записей было успешно экспортировано в файл:

Рассмотренные возможности Teradata SQL Assistant помогут эффективно работать с внешними данными в случаях, когда отсутствуют права на создание и запись объектов на промышленном сервере Teradata.

Источник

Teradata Volatile Table

Teradata Volatile tables act like a normal Teradata table but are volatile in nature. What does that mean?

The Volatile tables in Teradata are created by the user and used only the duration they are logged into the Teradata system and dropped automatically by Teradata Manager once the user disconnects from the session. Dropping here means, dropping both the definition and data inserted into the volatile table.

What is the use of Volatile Table?

Suppose you are working in the Teradata database for some performance analysis and you have to create quite a few tables for the same. One option is here to create normal Teradata tables, use those and drop them at the end. Another smart way for doing this create the volatile tables, use them and logoff. Teradata automatically drops those tables once you disconnect from the session.

Teradata Volatile Table Syntax

Following is the syntax for creating Teradata Volatile table.

Teradata Volatile Table Example

The following example will create a volatile table names student.

You can see that in the above example, we have mentioned ON COMMIT PRESERVE ROWS. This will preserve the data once you insert the data into it. The default value is ON COMMIT DELETE ROWS.

Insert Data into Volatile Table

Let’s insert some records into the volatile table.

Select data from Volatile Table

Next, we will run the Select Statement into the Volatile table.

Output:

At last, if we disconnect from the current session and after re-logging, run the same select statement again, we will find that the table student does not exist anymore.

Источник

Teradata Volatile Tables – How To Use Them

By Roland Wenzlofsky

What is a Teradata Volatile Table?

Volatile tables are very similar to derived tables, but their existence is not limited to their transaction; they stay materialized until the sessions in which they have been created ends.

Volatile tables are created with the following syntax:

An in-depth comparison between Volatile Tables, Global Temporary Tables, and Derived Tables can be found here:

Teradata Volatile Table Features

What kind of space does a Volatile Table use?

Volatile Tables are materialized in the spool space of the user.

Who can access a Volatile Table?

Volatile tables can only be accessed in the user’s session in which they were materialized.

Is there a DBC table where I can see which Volatile Tables I have materialized?

No, but with the command HELP VOLATILE TABLE, all materialized tables are listed.

Where is the DDL of a volatile table stored if not in the Data Dictionary (DBC)?

The table definition is kept in the AMP cache.

How long is a volatile table available?

Volatile tables are automatically removed when a session is terminated or a DROP TABLE statement is executed.

What happens if Teradata is restarted while I have materialized a volatile table?

Unfortunately, the table is lost along with its contents.

Can an ALTER TABLE be executed on a volatile table?

This is not possible.

Can I create a secondary index on a volatile table?

This is only possible when the table is created. Therefore, you cannot first create the table and then execute a CREATE INDEX statement.

Can I create a Join Index or Hash Index on a volatile table?

No, that’s not possible.

Who is the owner of a Volatile Table?

A Volatile Table belongs neither to a database nor to a user, but to the session of a user.

Can I create the same volatile table in different sessions?

Yes, but the content is session-specific, of course.

Can I use FALLBACK protection?

Yes, but this is not really useful because volatile sessions do not survive a system restart. It only makes sense to create a volatile table while an AMP is down.

Can a volatile table be used for performance tuning?

Volatile tables offer several possibilities for this. E.g., no DBC table is needed, and therefore, there can be no performance degradation if the competing workload on one of these tables holds e.g. a wide lock.

Can I create statistics on a volatile table?

Statistics are allowed, but when using the statement DIAGNOSTIC HELPSTAT ON FOR SESSION, we have to be aware that they are still proposed in the EXPLAIN plan. This is because they are not available to the optimizer in a DBC table.

Can a Volatile Table be created as a PPI table?

Yes, this is possible.

Teradata Volatile Table Restrictions

Roland Wenzlofsky

Have you tried adding ‘on commit preserve rows’ command?

I am having issues with accessing a volatile table within the same session. I can successfully CREATE a multiset volatile table, but when I try to import a text table using an INSERT statement, I am told the volatile table doesn’t exist. My task is run in three operations. First, the table definition is created using CREATE MULTISET VOLATILE TABLE cdwhist. Then, I turn on IMPORT DATA and run the INSERT INTO cdwhist process. Finally, the last step utilizes the cdwhist volatile table and joins it with a table on the database. It is the second step that’s failing.

Источник

How to Create Volatile Table in Teradata?

Volatile tables are as same as simple tables but with a small difference i.e. they are volatile in nature.

Like a simple table, the volatile table is also formed by the user and can only be used until the user is logged into. Once the user is logged off or disconnects, the Teradata manager automatically drops the table from the session.

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.

After dropping the table by Teradata manager, the data and definition inserted in the volatile table will be erased automatically.

How can you use the volatile table?

Suppose, you are the user and working with the Teradata database and you have to form a couple of tables in the same database. The first option is you have to create simple tables in the same database and drop them after use.

The second option is you can create volatile tables whose data and definition are automatically dropped by the Teradata database after you logged off from the database and that will be the more smart way.

We will follow the below syntax for volatile table syntax.

Syntax:

Example:

The following example will create a volatile table of the name ‘geek’.

Here, you can clearly see the last line written as ON COMMIT PRESERVE ROWS this line will preserve the data after inserting it by you.

The default value is ON COMMIT DELETE ROWS.

Data insertion in the volatile table:

Let’s insert some data in the volatile table.

Select data from the volatile table:

We will run the select statement into the volatile table.

Output:

At last, if we disconnect from the current session and after re-logging, run the same select statement again, we will find that the table student does not exist anymore in the database.

Источник

Русские Блоги

Основы SQL-Teradata

Основы SQL-Teradata

1. Создайте таблицу с той же структурой, что и таблица pnr_1.

Создать таблицу pnr_2 как pnr_1 без данных

Создать таблицу pnr_2 как pnr_1 с данными

2. NULLIF (exp1, exp2) возвращает ноль, если поля exp1 и exp2 равны, возвращает exp1, когда два не равны

——- Обычно используется, когда делитель не может быть 0 RPK / NULLIF (ASK, 0)

3. Перехват Substr (exp, m, n) от m-го бита, длина перехвата равна n

—— Обычно используется при обработке временных подстрок (‘20141023’, 1, 6) à ’201410’

4. COALESCE (expression_1, expression_2,…, expression_n) ссылается на каждое выражение параметра по очереди и останавливает и возвращает значение, когда оно встречает ненулевое значение. Если все выражения имеют нулевое значение, в конечном итоге будет возвращено нулевое значение.

Index (str1, str2) возвращает начальную позицию str2 в str1

Индекс (str1, str2)> 0 Имя таблицы содержит str2 в str1

6. Десятичная (n, m) эффективная длина составляет n десятичных знаков, учитывающих m

7. Принцип округления Teradata: правила округления в Teradata определяются параметром RoundHalfwayMagUp в DBSCONTROL. Значение по умолчанию RoundHalfwayMagUp = FALSE. В это время округление выполняется в соответствии с правилом Teradata. Правило Teradata считает, что 5 является промежуточным значением, и вероятность не равна даже всем округлениям. Если это зависит от четности предыдущего бита, округленные 5 и округленные 5. являются средними.В соответствии с этим принципом правило округления Teradata: когда есть бит после 5, который не равен 0, тогда переносите, когда 5 Когда все они равны 0 или других битов нет, в соответствии с четностью предыдущего бита 5, нечетные и четные не будут введены.

8. Когда две таблицы объединены, типы данных соответствующих полей должны быть полностью согласованными.

—— Буквы и китайские иероглифы не могут быть объединены

—— При объединении 958 и 0.123 значение 0.123 будет принудительно преобразовано в 0, поэтому убедитесь, что тип данных соответствует

9. Замените прежнее место («тера-данные», «-», «») === «терадата».

minus sleect A,B,C from table_1 where E=1

—— Удалить данные, содержащиеся в результате запроса 2 из результата запроса 1

Дата первого месяца предыдущего года текущей даты

Преобразовать десятичные дроби в проценты

cast(0.1234*100 as decimal(20,2))||’%’ ===》 12.34%

Таблица переименовать переименовать таблицу tablename в newtablename

Справочная база данных pmart отображает объекты в библиотеке pmart (таблицы, представления, макросы и т. Д.)

Select * from table_1 where name(CASESPECIFIC) like ‘%M%’

——— teradata не чувствительна к регистру в состоянии по умолчанию

%, _ Используется как символ подстановки в лайке, но при поиске% и _ сам

Определите ESCAPE символ% и _ после того, как escape-символ используется в качестве общих символов

like ‘B% ‘ESCAPE’ B ‘—- любой конец строки символов со вторым символом как _

char (M) символьная строка фиксированной длины занимает в общей сложности M байтов. Когда символьная строка меньше M байтов, она дополняется пробелами; varchar (M) символьная строка переменной длины Максимальная длина составляет M

Функции, связанные с датой

EXTRACT — select extract( year/month/day from date+30)

——select UPPER(name) ;select name(UPPERCASE)

Create MACRO macro_name

Изменить определение макроса

ЗАМЕНИТЕ МАКРО макро-имя (параметр макроса) AS (………)

DROP MACRO pmart.macro_mkt_dom_reg_detail

КОММЕНТАРИЙ О МАКРО pmart.macro_mkt_dom_reg_detail IS «Международный отчет»

—- Результат подзапроса уникален, без повторяющихся значений, что эквивалентно добавлению различных

—- порядок не может быть использован для подзапроса

Выберите pax_count DECIMAL (20,6) (НАЗВАНИЕ «Количество перевезенных пассажиров»)

Select * from table_1 where CHARACTERS(first_name)>5

Select * from tbale_1 where CHARACTERS( TRIM(frist_name) )>5

select trim (’ abc ‘) ===》 abc

выберите обрезку (оба ‘a’ из ‘aabacdea’) ===》 bacde удалить первый и последний указанные символы

выберите обрезку (ведущий ‘a’ из ac aabacdea ’) ===》 bacdea удаляет указанные в начале символы

выбрать обрезку (завершающий ‘a’ из ‘aabacdea’) ===》 aabacde удалить указанный символ в конце

Когда таблица связана с самим собой, то есть само-соединение (self join), таблица должна использовать псевдоним

CREATE VOLATILE MULTISET TABLE VT_RPT_OD_FORWARD_BOOKING

ON COMMIT PRESERVE ROWS;

CHARACTER SET UNICODE CASESPECIFIC

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

Постоянное ведение журнала

Проверьте ограничение (Проверьте)

Столбец по умолчанию

По умолчанию по умолчанию установлено значение SET TABLE. При вставке SET TABLE необходимо проверить, есть ли в таблице дублирующиеся данные, чтобы она потребляла много ресурсов. Поэтому при построении таблицы следует использовать MULTISET TABLE.

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

CREATE VOLATILE MULTISET TABLE vt_airport_city_cnty_region AS

PRIMARY INDEX (airport_iata_cd)

ON COMMIT PRESERVE ROWS;

UNION —Автоматическое удаление повторяющихся записей после объединения двух наборов

drop table table_name удаляет содержимое таблицы и ее определение в словаре данных

delect table_name / delect from table_name только удалить данные в таблице

Удалить записи, которые соответствуют определенному условию: delect из table_name, где age 20 and age 20 and age 999

36. Операции сбора (операции сбора нельзя использовать в подзапросах)

Create view view_name as select * from table_name

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

(1) Если представление получено из более чем двух основных таблиц, представление не может быть обновлено.

(2) Если поля представления получены из выражений или констант полей, операции INSERT и UPDATE в этом представлении запрещены, но операции DELETE разрешены.

(3) Если поле представления происходит из заданной функции, представление не может быть обновлено.

(4) Если предложение GROUP BY включено в определение представления, представление не может быть обновлено.

(5) Если фраза DISTINCT включена в определение представления, представление не может быть обновлено.

(6) Если в определении представления есть вложенные запросы, и таблица, включенная в предложение FROM внутреннего запроса, также является базовой таблицей, которая экспортирует представление, это представление не может быть обновлено.

Базовая таблица системного календаря Sys_calendar.Caldates

calendar_date DATE UNIQUE (стандартная дата Teradata)

day_of_week BYTEINT, (1-7, день недели, 1 представляет воскресенье)

day_of_month BYTEINT, (1-31, номер месяца)

day_of_year SMALLINT, (1-366, день года)

weekday_of_month BYTEINT, (Количество вхождений дня недели в этом месяце)

week_of_month BYTEINT, (неделя месяца, с воскресенья по субботу как неделя. 0, таблица

(Показывает первую незавершенную неделю месяца; 1 означает первую полную неделю месяца)

week_of_year BYTEINT, (0-53) (неделя года, 0 означает первую неполную неделю)

month_of_quarter BYTEINT, (1-3, месяц квартала)

month_of_year BYTEINT, (1-12, месяц года)

month_of_calendar INTEGER, (1-n, месяц в этом календаре, начиная с января 1900 г.)

quar_of_year BYTEINT, (1-4, квартал года)

quar_of_calendar INTEGER, (четверть этого календаря, начиная с января 1900 года)

year_of_calendar SMALLINT, (год, с 1900)

Накопить номер рейса с flight_dt

Нажмите flight_dt, чтобы накапливать номер рейса на разных маршрутах, то есть повторно накапливать на разных маршрутах

Сортировать по разным авиакомпаниям

Условно ограничить результаты после ранжирования: квалифицироваться

Позиция в порядке убывания (flight_profit ASC). В настоящее время Quality> 10 занимает первые 10 строк в списке (то есть, 10 самых низких flight_profit)

Select rank(flight_profit) as rank_1 from table_name

Выберите 10 лучших данных

41. Квантиль (в порядке возрастания согласно order_list каждая запись находится в квантиле позиции)

Квантиль (quantile_constant, order_list ASC), упорядоченный в порядке убывания order_list, причем наибольшее значение находится спереди, а наименьшее квантиль (0)

Квантиль (quantile_constant, order_list_1, order_list_2) Если значение order_list1 для двух записей одинаковое, выполните сортировку в порядке возрастания в соответствии со значением order_list2, а затем определите соответствующий квантиль

43. Библиотека импорта данных

Сохраните файл внешних данных в виде файла Excel в формате CSV, а затем сохраните файл CSV в виде текстового файла TXT.

Создать новую таблицу данных для импорта в библиотеку

Вставьте в new_table_name («Поле 1», «Поле 2», «Поле 3») значения (. )

44. Повторяющиеся данные в справочной таблице

Select flight_dt, airline_cd,rpk,ask from table_name where flight_dt=’20141128’ group by 1,2,3,4 having count(*)>1

45. Переменная временная таблица

Вы можете использовать команду HELP VOLATILE TABLE, чтобы получить все переменные временные таблицы, которые существуют в сеансе

Информация. (Примечание. Команда HELP DATABASE не будет отображать переменные временные таблицы, поскольку словарь данных не записывает переменные временные таблицы.)

Переменная временная таблица не может: использовать журнал доступа, переименовать, использовать утилиту Multiload или Fastload для загрузки

46. ​​Неуникальный вторичный индекс UNSI

Неуникальный вторичный индекс (NUSI) является разновидностью индекса Teradata, неосновного индекса, значение столбца индекса может быть не уникальным. Как правило, использование индексированных столбцов в предложении WHERE повышает производительность запросов. Создайте неуникальный вторичный индекс, вы можете использовать синтаксис CREATE TABLE для создания с таблицей, вы также можете использовать синтаксис CREATE INDEX для создания после создания таблицы. Если индекс больше не нужен, вы можете использовать DROP INDEX для удаления индекса.

Создается неуникальный вторичный индекс, и на каждом AMP создается вложенная таблица. В подстоле хранятся некоторые записи, включая каждое значение индекса и номер записи (id-строки) записи базовой таблицы, а записи в под-таблице сортируются и сохраняются в соответствии со значением индекса хеш-значения. Таким образом, очень удобно находить записи по значению индекса, но для поиска по диапазону индекс бесполезен. Например, при использовании вышеуказанного индекса при опросе сотрудников с рабочим кодом 122100 индекс работает, а при запросе сотрудников с рабочим кодом от 122000 до 123000 индекс не работает.

Создайте неуникальный вторичный индекс:

(2) Создать неуникальный вторичный индекс, когда таблица уже существует

Создать индекс (имя поля) для таблицы _ имя

Удалите неуникальный вторичный индекс таблицы:

——— Неуникальный вторичный индекс, отсортированный по значению

Подтаблица индекса неуникального вторичного индекса (Value Ordered NUSI), отсортированного по значению, сохраняет записи по значению данных, а не по значению хеш-функции. Этот индекс очень полезен при запросах по диапазону.

(1) Сразу добавить порядок индекса (имя поля) по значениям (имя поля) после построения оператора

(2) Когда таблица уже существует

Создать порядок индекса (имени поля) по значениям (имени поля) для table_name

Неуникальные столбцы вторичного индекса, отсортированные по значению, должны быть:

! Принадлежит столбцу в определении индекса

Примечание. Хотя тип данных DECIMAL разрешен, длина не может превышать 4 байта и не может иметь десятичных дробей.

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

CREATE JOIN INDEX cust_ord_ix AS

FROM customer c INNER JOIN orders o

ON c.cust_id = o.cust_id

Индекс соединения состоит из двух частей: фиксированной части (в первой скобке) и повторяемой части (во второй скобке).

47. Загрузка внешних данных (небольшой объем данных, несколько полей)

(1) Сохранить внешний файл данных типа CSV в виде текстового файла TXT

—— Убедитесь, что разделитель, распознаваемый помощником Teradata, является запятой

Insert into ptest.corp_name (sort_num,corp_name) values (. ) ;

rank() over (partition by calss order by age )

(Сортировка записей по группам полей) Сначала группа по классу, затем сортировка по возрасту в каждом классе

row_number() over (partition by class order by age )

(Сортировка записей по группам полей) Сначала группа по классу, затем сортировка по возрасту в каждом классе

sum() over (partition by calss order by score )

(Объедините записи в соответствии с полем группировки) Сначала группируйте по классам, затем суммируйте баллы

49. Объяснить (выбрать ……) возвращает шаг выполнения SQL после обработки оптимизации, который является только шагом выполнения, на самом деле не выполненным

AS REV from PMART.APP_OTH_AIR_BILL

where substr(summ_dt,1,4)=‘2013’ and airline_cd=‘UA’ group by 1,2,3 ORDER BY 2,3)

First, we lock a distinct PMART.“pseudo table” for read on a

RowHash to prevent global deadlock for PMART.APP_OTH_AIR_BILL.

Next, we lock PMART.APP_OTH_AIR_BILL for read.

We do an all-AMPs SUM step to aggregate from

PMART.APP_OTH_AIR_BILL by way of an all-rows scan with a condition

of («(PMART.APP_OTH_AIR_BILL.Airline_Cd = ‘UA’) AND

, grouping by field1 ( PMART.APP_OTH_AIR_BILL.Airline_Cd

,PMART.APP_OTH_AIR_BILL.ARR_AIRPORT_CD). Aggregate Intermediate

Results are computed globally, then placed in Spool 3. The size

of Spool 3 is estimated with no confidence to be 283,494 rows (

12,757,230 bytes). The estimated time for this step is 0.11

We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of

an all-rows scan into Spool 1 (group_amps), which is built locally

on the AMPs. Then we do a SORT to order Spool 1 by the sort key

in spool field1 (PMART.APP_OTH_AIR_BILL.DEP_AIRPORT_CD,

PMART.APP_OTH_AIR_BILL.ARR_AIRPORT_CD). The size of Spool 1 is

estimated with no confidence to be 283,494 rows (14,741,688 bytes).

The estimated time for this step is 0.03 seconds.

Finally, we send out an END TRANSACTION step to all AMPs involved

in processing the request.

-> The contents of Spool 1 are sent back to the user as the result of

Источник