Работа с переменными
Переменные могут быть заданы двумя способами:
Если уже была определена &&variable, то значение будет подставлено во все дальнейшие переменные как &variable так и &&variable.
Если была определена &&variable, и скрипт запущен повтороно в ходе той же сессии работы с SQLplus — будет использовано старое значение переменной. Чтобы этого избежать — можно запрашивать интерактивный ввод в скрипте принудительно, испольтзуя команду:
ACCEPT можно использовать для валидации:
Для ввода дат в определенном формате:
SQL*Plus поддерживает четыре типа переменных: CHAR, NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. При вводе с клавиатуры переменная будет типа CHAR.
Несмотря на это, можно использовать NEW_VALUE, чтобы задать числовую переменную, полученную как результат запроса.
Bind-переменные могут использоваться для передачи данных между PL/SQL и SQL блоками:
Присвоить bind-переменной значение &-переменной:
Вывести значение bind-переменной:
Присвоить &-переменной значение bind-переменной:
Получаем OUT-параметр процедуры в bind-переменную:
Условное выполнение в SQLplus:
Пример скрипта, принимающего несколько переменных на вход в формате c возможностью задать дефолтные значения:
Introduction
Oracle is a database that is widely used in the IT industry due to its extensive data management capabilities. In the era of big data, there is a surging demand for analytics. There are now, several tools to perform ETL on the data so that it can be analyzed and reports can be generated. Some of these tools need extracted data whereas some can directly connect to the data and extract it. The data which is extracted in CSV (Comma Separated Value) has gained popularity in recent times.
In this article, we will demonstrate to you, how to perform Oracle SQLPlus export to CSV data. We will also show you a simpler alternative, Hevo Data, a No-code platform that can perform ETL much faster.
However, before getting into Oracle SQLPlus export to CSV data, let us first understand Oracle and its features in brief.
Из командной строки MySQL
Если у вас есть только доступ из командной строки к экземпляру MySQL, а не доступ к самому серверу (например, когда он не управляется вами, в случае Amazon RDS), проблема немного сложнее. Пока вы можете использовать на сервере для создания списка, разделенного запятыми, интерфейс командной строки MySQL по умолчанию разделяется на вкладки.
Просто введите запрос из командной строки и направьте его в файл:
mysql -u root -e "select * from database;" > output.tsv
Поскольку выходные данные MySQL разделены вкладками, это называется файлом TSV для «значений, разделенных табуляцией», и может работать вместо вашего файла CSV в некоторых программах, таких как импорт электронных таблиц. Но это не файл CSV, и конвертировать его в один сложно.
Вы можете просто заменить каждую вкладку запятой, которая будет работать, но приведет к сбою, если во входных данных есть запятые. Если вы на 100% уверены, что в вашем файле TSV нет запятых (обратитесь к ), вы можете заменить вкладки на :
sed "s/t/,/g" output.tsv > output.csv
Но если у вас есть запятые в ваших данных, вам придется использовать намного длиннее регулярное выражение:
sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv
Это будет правильно экранировать поля с кавычками, что решит проблему с запятыми.
Примечание: символ табуляции является не стандарт, В macOS и BSD он недоступен, что приводит к путанице в каждой строчной букве «t», вызывающей вставить ошибочные запятые. Чтобы решить эту проблему, вам нужно использовать буквенный символ табуляции вместо :
sed "s/ /,/g" output.tsv > output.csv
Если ваши входные данные содержат вкладки, вам не повезло, и вам придется самостоятельно создавать файл CSV с использованием языка сценариев.
Использование DictReader
Эта функция используется для вывода данных из текстового файла в виде словаря.
У нас есть файл sample7.txt со следующими данными об учениках.
Примечание. Не обязательно сохранять файл с расширением .csv, можно и в других форматах. Главное, чтобы в самом файле использовался простой текст и данные оставались в первозданном виде.
Теперь воспользуемся приведенным ниже кодом, чтобы прочитать данные и вывести их в формате словаря. Вся методика одинакова, только вместо используется .
csv_file = csv.DictReader(file)
Во время выполнения можно видеть, что данные печатаются в виде словаря. Данная функция преобразует каждую строку в словарь.
Exporting CSV Data From Oracle
Before you start, do take a look at the schema:
- The schema defined is HR.
- The table’s name is Employee.
- The ‘Employee’ table contains the following attributes:
- “employee_id” – Primary key, which represents the ID of an employee.
- “first_name” – First name of the employee.
- “last_name” – Last name of the employee.
- “email” – Email address of the employee.
- “department_id” – Foreign key for department schema.
Oracle SQLPlus Export To CSV Using SPOOL Command
SQLPlus is an interface to query the data present in Oracle DB. It is available in the form of a command-line tool and a Windows-based GUI tool. The SPOOL command will be used to perform Oracle SQLPlus export to CSV data. Before beginning the steps to use the SPOOL command, there are a few configurations that need to be set.
Set The Configurations
You can change these settings at the runtime or by setting them as default in the User Profile in the login.sql file, which comes with the installation.
The variables are:
Let’s briefly discuss the usage of each of the above mentioned attributes:
- colsep is the column separator (delimiter) used to separate the fields in the CSV file. For this example, a comma is used. However, different separators can be used as well.
- headsep is required when the user wants to publish a header in the output file. In this example, this is set to off as a header is not needed.
- pagesize is the parameter used to control the number of lines per page in the output. Since you are writing to a file, set this number to 0.
- trimspool is set to on to remove trailing whitespace.
Run The Query
Once you set the required configuration, you need to enable the SPOOL command to spool the output to the CSV file. The syntax is:
The query to obtain the result set will be something like:
You need to set the SPOOL command to off after the execution. The syntax is:
The combination of all the settings and queries will look like this:
The Oracle SQLPlus export to CSV is finally accomplished.
An Alternative: Using Hevo Data
The previous method is one way of Oracle SQLPlus export to CSV. But not everyone would be interested in going through the messy and convoluted process of working with custom scripts. You might be interested in getting your CSV files out of Oracle painlessly into a destination and moving on with your business. There is a very robust and intuitive solution to accomplish the same.
Hevo Data is a modern ETL tool with extensive capabilities to extract data from a source (Oracle DB in this case) to a destination platform or a data warehouse.
Hevo Data, a No-code Data Pipeline, is a fully managed data integration solution that helps users migrate data from multiple sources like databases and cloud applications to their data warehouses.
Steps To Export Oracle Database Using Hevo Data
- Using the Hevo Data platform, connect to the Oracle database using connectors (Hevo has an extensive library of connectors) and configure it as a source by providing the URL, username, password, and other credentials.
- Configure the destination data warehouse (such as Redshift, Snowflake, etc.) and start moving data instantly!
Понимание операторов SELECT
Как упоминалось во введении, SQL-запросы почти всегда начинаются с оператора . SELECT используется в запросах, чтобы указать, какие столбцы из таблицы должны быть возвращены в наборе результатов. Запросы также почти всегда включают , который используется для указания таблицы, к которой будет обращаться оператор.
Как правило, SQL-запросы следуют этому синтаксису:
Например, следующий оператор вернет весь столбец из таблицы:
Вы можете выбрать несколько столбцов из одной таблицы, разделяя их имена запятыми, например:
Вместо того, чтобы называть конкретный столбец или набор столбцов, вы можете следовать за оператором со звездочкой (), которая служит заполнителем, представляющим все столбцы в таблице. Следующая команда возвращает каждый столбец из таблицы :
WHERE используется в запросах для фильтрации записей, которые удовлетворяют указанному условию, и любые строки, которые не удовлетворяют этому условию, исключаются из результата. Предложение обычно соответствует следующему синтаксису:
Оператор сравнения в предложении WHERE определяет способ сравнения указанного столбца со значением. Вот некоторые распространенные операторы сравнения SQL:
| Оператор | Что он делает |
|---|---|
| = | тесты для равенства |
| != | тесты для неравенства |
| тесты для меньше, чем | |
| > | тесты для больше |
| тесты для менее чем или равный к | |
| >= | тесты для больше чем или равный к |
| BETWEEN | проверяет лежит ли в заданном диапазоне |
| IN | проверяет содержатся ли строки в наборе значений |
| EXISTS | тесты на соответствие строки существует при заданных условиях |
| LIKE | проверяет совпадает ли значение с указанной строкой |
| IS NULL | тесты для `NULL` значения |
| IS NOT NULL | тесты для всех других значений, чем `NULL` |
Например, если вы хотите найти размер обуви Ирмы, вы можете использовать следующий запрос:
SQL допускает использование подстановочных знаков, и это особенно удобно при использовании в предложениях WHERE. Знаки процента () представляют ноль или более неизвестных символов, а подчеркивания () представляют один неизвестный символ. Они полезны, если вы пытаетесь найти конкретную запись в таблице, но не уверены, что эта запись. Чтобы проиллюстрировать это, скажем, что вы забыли любимое блюдо нескольких своих друзей, но вы уверены, что это конкретное блюдо начинается с буквы «t». Вы можете найти его имя, выполнив следующий запрос:
Основываясь на вышеприведенном выводе, мы видим, что блюдо — это тофу.
Могут быть случаи, когда вы работаете с базами данных, в которых есть столбцы или таблицы с относительно длинными или трудно читаемыми именами. В этих случаях вы можете сделать эти имена более читабельными, создав псевдоним с ключевым словом . Псевдонимы, созданные с помощью , являются временными и существуют только на время запроса, для которого они созданы:
Здесь мы сказали SQL отображать столбец как, столбец как, а столбец как .
Примеры, которые мы рассмотрели до этого момента, включают в себя некоторые из наиболее часто используемых ключевых слов и предложений в запросах SQL. Они полезны для базовых запросов, но они бесполезны, если вы пытаетесь выполнить вычисление или получить скалярное значение (одно значение, а не набор из нескольких различных значений) на основе ваших данных. Это где агрегатные функции вступают в игру.
Чтение¶
Пример чтения файла в формате CSV (файл csv_read.py):
import csv
with open('sw_data.csv') as f
reader = csv.reader(f)
for row in reader
print(row)
Вывод будет таким:
$ python csv_read.py
В первом списке находятся названия столбцов, а в остальных
соответствующие значения.
Обратите внимание, что сам csv.reader возвращает итератор:
In 1]: import csv
In 2]: with open('sw_data.csv') as f
... reader = csv.reader(f)
... print(reader)
...
<_csv.reader object at 0x10385b050>
При необходимости его можно превратить в список таким образом:
In 3]: with open('sw_data.csv') as f
... reader = csv.reader(f)
... print(list(reader))
...
, 'sw1', 'Cisco', '3750', 'London'], 'sw2', 'Cisco', '3850', 'Liverpool'], 'sw3', 'Cisco', '3650', 'Liverpool'], 'sw4', 'Cisco', '3650', 'London']]
Чаще всего заголовки столбцов удобней получить отдельным объектом. Это
можно сделать таким образом (файл csv_read_headers.py):
import csv
with open('sw_data.csv') as f
reader = csv.reader(f)
headers = next(reader)
print('Headers: ', headers)
for row in reader
print(row)
Иногда в результате обработки гораздо удобней получить словари, в
которых ключи — это названия столбцов, а значения — значения столбцов.
Для этого в модуле есть DictReader (файл csv_read_dict.py):
import csv
with open('sw_data.csv') as f
reader = csv.DictReader(f)
for row in reader
print(row)
print(row'hostname'], row'model'])
Вывод будет таким:
$ python csv_read_dict.py
{'hostname': 'sw1', 'vendor': 'Cisco', 'model': '3750', 'location': 'London, Globe Str 1 '}
sw1 3750
{'hostname': 'sw2', 'vendor': 'Cisco', 'model': '3850', 'location': 'Liverpool'}
sw2 3850
{'hostname': 'sw3', 'vendor': 'Cisco', 'model': '3650', 'location': 'Liverpool'}
sw3 3650
{'hostname': 'sw4', 'vendor': 'Cisco', 'model': '3650', 'location': 'London, Grobe Str 1'}
sw4 3650
Conclusion
This article presents a method of Oracle SQLPlus export to CSV. While the Oracle SQLPlus export to CSV is one way to do it, if you are not interested in writing custom scripts manually, you have a fully automated, easy-to-use alternative– Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built Integrations that you can choose from.
visit our website to explore hevo
Hevo can help you Integrate your data from numerous sources like Oracle and load them into a destination to Analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.
SIGN UP for a 14-day free trial and see the difference!
Функции модуля Python CSV
Модуль CSV используется для обработки файлов CSV для чтения / записи и получения данных из указанных столбцов. Существуют следующие типы функций CSV:
- csv.field_size_limit – возвращает текущий максимальный размер поля, разрешенный парсером.
- csv.get_dialect – возвращает диалект, связанный с именем.
- csv.list_dialects – возвращает названия всех зарегистрированных диалектов.
- csv.reader – читает данные из файла csv.
- csv.register_dialect – связывает диалект с именем. Имя должно быть строкой или объектом Unicode.
- csv.writer – записывает данные в файл csv.
- o csv.unregister_dialect – удаляет диалект, связанный с именем, из реестра диалектов. Если имя не является зарегистрированным именем диалекта, возникает ошибка.
- csv.QUOTE_ALL – предписывает объектам записи заключать в кавычки все поля. csv.QUOTE_MINIMAL – предписывает объектам записи указывать только те поля, которые содержат специальные символы, такие как quotechar, delimiter и т. д.
- csv.QUOTE_NONNUMERIC – предписывает объектам записи заключать в кавычки все нечисловые поля.
- csv.QUOTE_NONE – указывает объекту записи никогда не заключать поля в кавычки.
Указание разделителя¶
Иногда в качестве разделителя используются другие значения. В таком
случае должна быть возможность подсказать модулю, какой именно
разделитель использовать.
Например, если в файле используется разделитель (файл
sw_data2.csv):
hostname;vendor;model;location sw1;Cisco;3750;London sw2;Cisco;3850;Liverpool sw3;Cisco;3650;Liverpool sw4;Cisco;3650;London
Достаточно просто указать, какой разделитель используется в reader (файл
csv_read_delimiter.py):
import csv
with open('sw_data2.csv') as f
reader = csv.reader(f, delimiter=';')
for row in reader
print(row)
Чтение файлов CSV
Python предоставляет различные функции для чтения файла CSV. Опишем несколько методов для чтения.
Использование функции csv.reader()
В Python модуль csv.reader() используется для чтения файла csv. Он берет каждую строку файла и составляет список всех столбцов.
Мы взяли текстовый файл с именем python.txt, в котором есть разделитель по умолчанию(,) со следующими данными:
name,department,birthday month Parker,Accounting,November Smith,IT,October
Пример:
import csv
with open('python.csv') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
line_count = 0
for row in csv_reader:
if line_count == 0:
print(f'Column names are {", ".join(row)}')
line_count += 1
Выход:
Column names are name, department, birthday month Parker works in the Accounting department, and was born in November. Smith works in the IT department, and was born in October. Processed 3 lines.
В приведенном выше коде мы открыли python.csv с помощью функции open(). Мы использовали функцию csv.reader() для чтения файла, который возвращает итеративный объект чтения. Объект чтения состоял из данных, и мы повторили цикл, используя цикл for, чтобы распечатать содержимое каждой строки.
Модуль CSV и диалекты
Если мы начнем работать, используя одни и те же файлы csv с форматами функций в коде, это сделает код очень некрасивым и непоследовательным. Модуль CSV помогает использовать метод диалектов в качестве опции для удаления избыточности данных.
Рассмотрим в качестве примера те же исходные данные, но на этот раз разделенные символом . Мы хотим удалить этот символ, пропустить лишний пробел и использовать одинарные кавычки между соответствующими данными.
Используя следующую строчку кода, мы получим желаемый результат
csv.register_dialect(‘myDialect’, delimiter =’|’, skipinitialspace=True, quoting= csv.QUOATE_ALL)
Эта строка отличается, поскольку она определяет три основные функции, которые необходимо выполнить. Из выходных данных вы можете видеть, что символ удаляется, и также добавляются одинарные кавычки.
Команда SQLPROMPT
Администратору баз данных обычно приходится иметь дело с несколькими базами данных. Из-за этого при выполнении множества задач на протяжении дня очень легко забыть, к какой базе данных подключен тот или иной сеанс SQL*Plus. Поэтому во избежание допущения грубых ошибок (вроде удаления производственных таблиц вместо разрабатываемых или тестируемых), следует всегда настраивать среду так, чтобы имя экземпляра базы данных постоянно отображалось в приглашении, напоминая о том, с какой базой данных происходит взаимодействие.
Для настройки приглашения SQL*Plus так, чтобы в нем отображалось имя базы данных, служит приведенная ниже команда, в которой используется специальная предопределенная переменная (предопределенные переменные подробно рассматриваются в разделе “Предопределенные переменные SQL*Plus” далее в главе):
SQL> SET SQLPROMPT '_CONNECT_IDENTIFIER > ' nick >
Обратите внимание, что команда приводит к немедленному изменению приглашения в интерфейсе SQL*Plus. После выдачи этой команды приглашение приобретает более значимый вид, ясно указывающий на то, с какой базой данных происходит взаимодействие, и избавляет от необходимости делать по этому поводу какие-либо предположения
В данном примере оно указывает на то, что в текущий момент подключение установлено с базой данных .
Для настройки приглашения SQL*Plus можно также использовать и другие специальные предопределенные переменные. Например, с помощью переменной в приглашении отображается имя пользователя:
SQL> SET SQLPROMPT "_USER > " APPOWNER >
Применив одновременно переменные и , можно сделать так, чтобы в приглашении отображалось и имя базы данных, и имя текущего пользователя:
SQL> SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > " APPOWNER@nick >
Используя переменные и , в приглашении можно отображать не только имя текущего пользователя, но и привилегии, которыми он обладает:
SQL> SET SQLPROMPT "_USER _PRIVILEGE> " SYS AS SYSDBA>
С помощью переменных , и в приглашении можно отобразить имя пользователя, текущую дату и имя базы данных:
SQL> SET SQLPROMPT "_USER 'on' _DATE 'at' _CONNECT_IDENTIFIER > " SYS on 20-JUN-09 at nick>
При желании строку, вроде показанной выше, легко добавить в файл ,чтобы желаемые значения устанавливались автоматически при каждом входе в систему, и их не приходилось настраивать вручную.
Создание образца базы данных
Прежде чем мы сможем начать делать запросы в SQL, мы сначала создадим базу данных и пару таблиц, а затем заполним эти таблицы некоторыми примерами данных. Это позволит вам получить практический опыт, когда вы начнете делать запросы позже.
Для примера базы данных, которую мы будем использовать в этом руководстве, представьте следующий сценарий:
Вы и несколько ваших друзей празднуете свои дни рождения друг с другом. В каждом случае члены группы направляются в местный боулинг, участвуют в дружеском турнире, а затем все направляются к вам, где вы готовите любимое блюдо для именинника.
Теперь, что эта традиция продолжается некоторое время, вы решили начать отслеживать записи с этих турниров. Кроме того, чтобы упростить планирование обедов, вы решаете создать запись о днях рождения ваших друзей и их любимых блюдах, сторонах и десертах. Вместо того чтобы хранить эту информацию в физической книге, вы решаете использовать свои навыки работы с базами данных, записав ее в базу данных MySQL.
Если вы создали сервер в NetAngels на основе образа Ubuntu 18.04 Bionic LAMP, то откройте приглашение MySQL выполнив от пользователя root команду:
mysql
Примечание: Если зайти в MySQL таким образом не удается, то для аутентификации с использованием пароля используйте команду:
mysql -u root -p
Затем создайте базу данных, запустив:
Затем выберите эту базу данных, набрав:
Затем создайте две таблицы в этой базе данных. Мы будем использовать первую таблицу, чтобы отслеживать записи ваших друзей в боулинге. Следующая команда создаст таблицу под названием «tourneys» со столбцами для «name» каждого из ваших друзей, количества турниров, которые они выиграли («wins»), их лучший результат за все время и каков размер обувь для боулинга, которую они носят ():
Как только вы запустите команду и заполните ее заголовками столбцов, вы получите следующий вывод:
Заполните таблицу ‘tourneys’ некоторыми примерами данных:
Вы получите такой вывод:
После этого создайте еще одну таблицу в той же базе данных, которую мы будем использовать для хранения информации о любимых блюдах ваших друзей на день рождения. Следующая команда создает таблицу с именем dinners и столбцами для«имя» каждого из ваших друзей, их «дата рождения», их любимое «блюдо», их любимое «гарнир» и их любимый «десерт»:
Аналогично для этой таблицы вы получите отзыв, подтверждающий успешное выполнение команды:
Заполните эту таблицу также некоторыми примерами данных:
Как только эта команда завершится успешно, вы закончили настройку базы данных. Далее мы рассмотрим основную структуру команд запросов SELECT.
С помощью Pandas
Это так же просто, как прочитать файл CSV с помощью pandas. Вам необходимо создать DataFrame, который представляет собой двумерную неоднородную табличную структуру данных и состоит из трех основных компонентов: данных, столбцов и строк. Здесь мы берем для чтения немного более сложный файл под названием hrdata.csv, который содержит данные о сотрудниках компании.
Name,Hire Date,Salary,Leaves Remaining John Idle,08/15/14,50000.00,10 Smith Gilliam,04/07/15,65000.00,8 Parker Chapman,02/21/14,45000.00,10 Jones Palin,10/14/13,70000.00,3 Terry Gilliam,07/22/14,48000.00,7 Michael Palin,06/28/13,66000.00,8
Пример –
import pandas
df = pandas.read_csv('hrdata.csv',
index_col='Employee',
parse_dates=,
header=0,
names=)
df.to_csv('hrdata_modified.csv')
Выход:
Employee, Hired, Salary, Sick Days John Idle, 2014-03-15, 50000.0,10 Smith Gilliam, 2015-06-01, 65000.0,8 Parker Chapman, 2014-05-12, 45000.0,10 Jones Palin, 2013-11-01, 70000.0,3 Terry Gilliam, 2014-08-12 , 48000.0,7 Michael Palin, 2013-05-23, 66000.0,8
Изучаю Python вместе с вами, читаю, собираю и записываю информацию опытных программистов.
Команда SPOOL
Команда позволяет сохранять вывод одного и более SQL-операторов в файлах операционной системы, как в UNIX, так и в Windows:
SQL> SET LINESIZE 180 SQL> SPOOL employee.lst SQL> SELECT emp_id, last_name, salary, manager FROM employee; SQL> SPOOL OFF;
По умолчанию создаваемые этой командой буферные (spooled) текстовые файлы сохраняются как . Хотя по умолчанию принято сохранять вывод в файле,его также можно отправлять и на принтер. Буферизация файлов является очень полезным приемом при использовании SQL для оказания помощи в написании SQL-сценариев, и некоторые примеры его применения можно найти в приложении.
С помощью команды можно добавлять данные в существующий буферный файл, а также полностью заменять его (по умолчанию происходит замена). Ниже приведен весь синтаксис этой команды:
SPOOL { имя_файла |REP|APP]| OFF | OUT }
Ниже описаны параметры команды .
- . Специфицирует имя буферного файла. Расширение может выглядеть по-разному, но в большинстве случаев используется предлагаемое по умолчанию расширение .lst.
- . Указывает, что требуется создать новый файл.
- . Указывает на необходимость замены содержимого существующего файла или создания нового файла, если он не существует. Это поведение по умолчанию.
- . Указывает на необходимость добавить содержимое буфера в конец указанного файла.
- . Указывает, что требуется остановить процесс буферизации.
- : Указывает, что требуется остановить процесс буферизации и отправить файл на принтер по умолчанию. В некоторых операционных системах этот параметр не поддерживается.
У команды имеет множество способов применения. Например, она легко экспортируется для перехвата результатов оператора . Перед этим, однако, должен обязательно задаваться формат вывода путем определения значений для переменных и . Ниже приведен пример:
SQL> SPOOL /u01/app/oracle/data/employees.txt; SQL> SELECT * FROM hr.employees; SQL> SPOOL OFF;
В этом примере файл служит для перехвата всех данных из таблицы . Далее его можно будет использовать для загрузки данных в другую таблицу с помощью утилиты SQL*Loader.
