#oracle #shell
#Oracle #оболочка
Вопрос:
Я пытаюсь загрузить данные в таблицу Oracle из файла csv с помощью сценария оболочки. Я хочу транспонировать данные и загрузить их в свою таблицу oracle.
Формат данных файла CSV: ниже приведены входящие данные, которые я получаю.
Date ,Emp_name,Math_zero,Math_max,Math_min,Math_avg,English_zero,English_max,English_min,English_avg
20161005,abc ,0 ,1 ,0 ,0 ,0 ,1 ,0 ,0
20161005,def ,0 ,1 ,0 ,0 ,0 ,1 ,0 ,0
Требуемый формат данных при загрузке их в таблицу Oracle:
Date ,emp_name,subject,subject_zero,subject_max,subject_min,subject_avg
20161005,abc ,Math ,0 ,1 ,0 ,0
20161005,def ,English,0 ,1 ,0 ,0
Моя таблица Oracle состоит из семи столбцов:
Date
Emp_name
Subject
Subject_zero
Subject_max
Subject_min
Subject_avg
Пожалуйста, предложите какой-нибудь сценарий оболочки для преобразования входящих данных в желаемый формат.
Спасибо Aggarwal
Комментарии:
1. Почему бы не использовать промежуточную таблицу и не выполнить все необходимые преобразования в базе данных?
Ответ №1:
Это можно сделать в SQL, после импорта строк (как они есть) в базу данных. Однако ваша входная строка имеет ужасную структуру, поэтому ее распутывание сложно.
Ваш «желаемый результат» имеет только две строки; разве не должно быть четырех строк, поскольку каждое emp_name имеет оценки по ОБОИМ предметам? Мой вывод состоит из четырех строк. Я также немного изменил тестовые данные, чтобы убедиться, что правильные значения попадают в правильные строки и столбцы; наличие последовательности оценок «0,1,0,0» для всех комбинаций emp_name / date и subject не помогает при отладке.
Я предположил, что входные строки могут содержать NULL (показано следующим образом: ‘1,0,1,1,,,,,» — две последовательные запятые означают НУЛЬ) для некоторых emp_name / дат и тем. Вот почему шаблон поиска для значений SUBJ_ …. настолько сложен.
Решение:
with
test_data ( str ) as (
select 'Date,Emp_name,Math_zero,Math_max,Math_min,Math_avg,'
|| 'English_zero,English_max,English_min,English_avg' || chr(10)
|| '20161005,abc,0,1,0,3,0,1,0,0' || chr(10)
|| '20161005,def,1,1,0,0,0,1,5,0'
from dual
),
line_split ( line_no, line_str ) as (
select level, regexp_substr(str, '[^' || chr(10) || '] ', 1, level)
from test_data
connect by level <= 1 regexp_count(str, chr(10))
),
cols ( idx, col ) as (
select level, regexp_substr(line_str, '[^,] ', 1, level)
from ( select line_str from line_split where line_no = 1 )
connect by level <= 1 regexp_count(line_str, ',')
),
subjects ( idx, subj ) as (
select ceil(idx/4), substr(col, 1, instr(col, '_') - 1)
from cols
where mod(idx, 4) = 3
)
select to_date(regexp_substr(l.line_str, '[^,] ', 1, 1), 'yyyymmdd') as dt,
regexp_substr(l.line_str, '[^,] ', 1, 2) as emp_name,
subj as subject,
to_number(regexp_substr(l.line_str, '([^,]*)(,|$)', 1, 4 * s.idx - 1, null, 1)) as subj_zero,
to_number(regexp_substr(l.line_str, '([^,]*)(,|$)', 1, 4 * s.idx , null, 1)) as subj_max,
to_number(regexp_substr(l.line_str, '([^,]*)(,|$)', 1, 4 * s.idx 1, null, 1)) as subj_min,
to_number(regexp_substr(l.line_str, '([^,]*)(,|$)', 1, 4 * s.idx 2, null, 1)) as subj_avg
from line_split l cross join subjects s
where l.line_no > 1
;
Вывод (с моими тестовыми данными):
DT EMP_NAME SUBJECT SUBJ_ZERO SUBJ_MAX SUBJ_MIN SUBJ_AVG
---------- -------- ---------- ---------- ---------- ---------- ----------
2016-10-05 abc Math 0 1 0 3
2016-10-05 abc English 0 1 0 0
2016-10-05 def Math 1 1 0 0
2016-10-05 def English 0 1 5 0
4 rows selected.
Комментарии:
1. Спасибо за ответ. Это решение будет работать для меня, если у меня будут входные данные в том виде, в каком они есть в моей базе данных. Но проблема здесь в том, что мы получаем этот тип входных данных в csv-файле, и нам нужно загрузить эти данные в таблицу oracle, используя таблицу Oracle скрипта unix: Date,emp_name,subject,subject_zero,subject_max,subject_min,subject_avg 20161005,abc ,Math,0 ,1 ,0 ,0 20161005,def,русский,0 ,1 ,0 ,0 Пожалуйста, предложите способ предварительной обработки данных из csv и загрузки их в таблицу Oracle с помощью скрипта unix.
2. Я предлагаю, чтобы строки были экспортированы в базу данных в том виде, в каком они есть, а затем обработаны в БД с помощью запроса, подобного тому, который я написал. Нет юридического требования о ТОМ, что вы ДОЛЖНЫ разделить входную строку в UNIX перед помещением результатов в БД (или есть?) То, что вы хотите сделать, будет намного медленнее по нескольким причинам.
3. Спасибо 🙂 Я загрузил необработанные данные в таблицу oracle, и теперь мне нужно будет выполнить sql-запрос, который вы предоставили. Не могли бы вы помочь, как мне обновить этот запрос, потому что в моих исходных данных у меня есть 160 разных тем и пять разных столбцов для каждой темы, как я упоминал выше. sub_null, sub_zero, sub_max, sub_min, sub_avg Мне нужно будет обработать данные из моей таблицы Oracle необработанных данных.. Спасибо
4. Пожалуйста, предоставьте мне решение для 160 предметов и пять вычислений для каждого sub_null sub_zero sub_max sub_min sub_avg sub_avg Я должен извлечь эти входные данные из таблицы oracle.
5. Привет, количество субъектов не имеет значения, оно вычисляется автоматически моим запросом (мой запрос НЕ предполагает фиксированного числа, например 2, субъектов, и ему не нужно заранее знать имена субъектов — все это вычисляется моим запросом непосредственно из данных, иуже выполняется запросом, который я опубликовал). Другая проблема — ПЯТЬ столбцов для каждого субъекта вместо ЧЕТЫРЕХ. Вы НИКОГДА не упоминали об этом (хотя вы говорите «как я упоминал выше» — это просто ложь, вы никогда раньше не упоминали ПЯТЬ столбцов). Что такое
sub_nullи чем оно отличается отsub_zero?
Ответ №2:
Я считаю, что лучший способ — использовать http://www.sql-workbench.net / если у вас есть JAVA (1.6 или 1.7), вы можете подготовить скрипт wbExport и выполнить его из командной строки. Этот инструмент может работать двумя способами с графическим интерфейсом и командной строкой. Вы также можете интегрировать его с оболочкой :
LOG_FILE=test.log
коснитесь ${LOG_FILE}
echo "`date %Y-%m-%d %H:%M:%S` STEP020 : Gathering script settings from database (BEGIN)" | tee -a ${LOG_FILE}
## DATABASE CONNECTION
CONFIG_SQL_FILE=${BASE_DIR}/deamon/sql/${DB2_CFG_SCRIPT}
truncate -s0 ${DB2_CFG_LOG} > /dev/null
echo " WbExport -file=${BASE_DIR}/deamon_settings.csv " > ${CONFIG_SQL_FILE}
echo " -type=text " >> ${CONFIG_SQL_FILE}
echo " -encoding='UTF-8' " >> ${CONFIG_SQL_FILE}
echo " -lineEnding='lf' " >> ${CONFIG_SQL_FILE}
echo " -header=false " >> ${CONFIG_SQL_FILE}
echo " -append=false " >> ${CONFIG_SQL_FILE}
echo " -dateFormat='yyyy-MM-dd' " >> ${CONFIG_SQL_FILE}
echo " -delimiter=';' " >> ${CONFIG_SQL_FILE}
echo " -timestampFormat='yyyy-MM-dd HH:mm:ss' " >> ${CONFIG_SQL_FILE}
echo " ; " >> ${CONFIG_SQL_FILE}
echo " " >> ${CONFIG_SQL_FILE}
echo "SELECT " >> ${CONFIG_SQL_FILE}
echo " HOST_ID, HOST_NAME, SCRIPT_PATH, CREATE_TS, UPDATE_TS, " >> ${CONFIG_SQL_FILE}
echo " HOST_DESC, HOST_MONIT_FREQ,HOST_MONIT_ENABLED, " >> ${CONFIG_SQL_FILE}
echo " PROCESS_MONIT_FREQ, PROCESS_MONIT_ENABLED " >> ${CONFIG_SQL_FILE}
echo "FROM " >> ${CONFIG_SQL_FILE}
echo " ${DB2_USERNAME}.SOME_TABLE " >> ${CONFIG_SQL_FILE}
echo "WHERE " >> ${CONFIG_SQL_FILE}
echo " LOWER(HOST_NAME) LIKE LOWER('${HOST_NAME}') AND " >> ${CONFIG_SQL_FILE}
echo " LOWER(SCRIPT_PATH) LIKE LOWER('${BASE_DIR}') " >> ${CONFIG_SQL_FILE}
echo "FETCH FIRST 1 ROWS ONLY; --limit to 1 row " >> ${CONFIG_SQL_FILE}
echo " " >> ${CONFIG_SQL_FILE}
echo " COMMIT; " >> ${CONFIG_SQL_FILE}
echo " " >> ${CONFIG_SQL_FILE}
if [ -f ${CONFIG_SQL_FILE} ]; then
echo "`date %Y-%m-%d %H:%M:%S` STEP020 : Failed to store setting file: ${CONFIG_SQL_FILE}" | tee -a ${LOG_FILE}
echo "`date %Y-%m-%d %H:%M:%S` STEP020 : Gathering script settings from database (FAILED)" | tee -a ${LOG_FILE}
exit 4;
fi
## DATABASE CONNECTION USED HERE
java -Djava.awt.headless=true -Xmx256m -classpath ${WORKBENCH_PATH}
-jar ${DB2_JAR}
-driverjar=${DB2_DRIVER}
-username=${DB2_USERNAME}
-password=${DB2_PASSWORD}
-url=${DB2_URL}
-script=${CONFIG_SQL_FILE}
-logfile=${DB2_CFG_LOG}
-fetchSize=100
-nosettings > /dev/null 2>amp;1
cat ${CONFIG_SQL_FILE} | sed "s/^/`date %Y-%m-%d %H:%M:%S` STEP020 : WORKBENCH /" | tee -a ${LOG_FILE}
if [ `cat ${DB2_CFG_LOG} | grep Error | wc -l` -gt 0 ]; then
echo "`date %Y-%m-%d %H:%M:%S` STEP020 : Failed to retrieve meta data (FAILED)" | tee -a ${LOG_FILE}
exit 5
fi
echo "`date %Y-%m-%d %H:%M:%S` STEP020 : Gathering script settings from database (END-OK)" | tee -a ${LOG_FILE}
Для работы с этим вам придется немного больше разбираться в SQLWorkbench, но, безусловно, это лучший инструмент, доступный сейчас. Вам также необходимо загрузить jdbc в вашу целевую базу данных.