IT-инфраструктура для бизнеса и творчества
Разработка
NTA

Сложности при выгрузке 500 гб из базы данных и пример их решения

Хочу поделиться с вами забавным случаем, который произошел со мной. Была поставлена задача — в кратчайшие сроки выгрузить 500 ГБ информации из базы данных (БД). Но на тот момент места на жестком диске катастрофически не хватало, и не было возможности оперативно очистить или добавить новый. К счастью, в наличии был защищенный файловый информационный ресурс (ФИР). Казалось бы, вот и решение — сохранять данные сразу в файл на ФИР. Но доступной скорости передачи информации на сетевой ресурс оказалось недостаточно, чтобы все выгрузить в полном объеме. У нас так: если SQL-запрос не отработал за сутки, он выпадает в ошибку из-за обрыва сессии. В итоге комичность в том, что на жесткий диск можно выгрузить быстро, но мало, а на ФИР много, но долго.

При учете всего вышеописанного, требовался инструмент, который мог выгружать из базы на жесткий диск определенный объем данных и перекладывать на ФИР без участия человека. Под такое описание подходил Python с библиотеками «cx_Oracle» и «shutil». Решение разделено на два скрипта:

Первый «Выгрузка данных»

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

За основу взят пример кода из предыдущей статьи. Ссылки на разные сайты:

Без изменений:

  1. Используемые библиотеки
  2. Проверка версии
  3. Дескриптор соединения с БД
  4. Указание логин/пароль
  5. Функция, в которой создается экземпляр класса connect
  6. Подключение к серверу

Внесенные изменения:

Функция, в которой создается курсор, выполняется запрос в БД и сохраняются данные в файл:

def dfFromOracle(connection, sql, file): us=0 outDF=pd.DataFrame() success = 'False' with connection.cursor() as cursor1: cursor1.execute(sql) #Отправка SQL-запроса в базу данных trn=10 while success == 'False' and trn>0: try: #Сохраняем колонки outheader=[desc[0] for desc in cursor1.description] header = ';'.join(_header)+'\n' myfile=open(file, 'w',encoding='UTF-8') myfile.writelines(header) myfile.close() #Цикл для выгрузки определенного количества строк While try: frame = cursor1.fetchmany(1000000) # Возвращаем определенное количество строк из результата запроса if not frame: #Проверяем наличие данных в массиве break outDF = pd.DataFrame(frame) #Формируем ДФ из массива outDF.to_csv(file, sep=';',encoding='UTF-8',mode='a',header=None,index=False) #Сохраняем сформированный ДФ success = 'True' us = 1 frame = '' #Очищаем массив except: trn=trn-1 print('Error') time.sleep(60) return us

Создаем массивы:

collection_id = ('31184','25597','***','68823') #Уникальное значение присвоенное массиву в БД year = ('2020','2019') #Год month = ('12','11','10','09','08','07','06','05','04','03','02','01') #Месяц npp = 0 #Номер по порядку выгруженных файлов для корректной сортировки new_file = '_op_.csv' #Неизменная часть имени файла

Цикл для формирования уникальных имен файлов, создания SQL-запросов и вызова функций:

print(str(time.ctime()) + ' : Старт') #Время старта for row_id in collection_id: for row_y in year: for row_m in month: npp += 1 #Счетчик выгруженных файлов #Собираем имя файла file = str(npp).rjust(5,'0') +'_'+ row_y +'_'+ row_m +'_'+ row_id + new_file #Собираем SQL-запрос sql = ("""select * from tabl where to_char(c_date, 'YYYY') = '""" + row_y + """' and to_char(c_date, 'MM') = '""" + row_m1 + """' and coll_id = '""" + row_tb + """'""") #Вызов функций with getConn(odsLogin, odsConnectStr) as con1: us = dfFromOracle(con1, sql, file) print(str(time.ctime()) + ' : Финиш') #Время финиша chek = open('Выгрузка_завершена_V.txt', 'a',encoding='UTF-8') #Создаем файл chek.close()

Второй «Перемещение выгруженных файлов»

Скрипт обеспечит постоянный контроль количества сохраняемых файлов. При превышении установленного лимита определит ранее выгруженные, скопирует в указанную папку и удалит из старой директории. Завершит свою работу после того как скрипт на выгрузку создаст файл «Выгрузка_завершена_V.txt».

Используем библиотеки:

import shutil import os import time

Создаем массивы:

path_file = r'\Users\ *** \ '[:-1] #Путь к выгруженным файлам на жестком диске save_file = r'\\ *** \ '[:-1] #Путь на ФИР к папке переноса chek = '' #Для проверки завершения цикла

Цикл мониторинга выгруженных файлов:

print(str(time.ctime()) + ' : Старт') #Время старта #Цикл поиска файлов while chek == '': count = 1 #Собираем в папке имена выгруженных файлов, которые необходимо перенести list_file = [] for file in os.listdir(path_file): if file.endswith(('op_.csv')): #Файл оканчивается на «op_.csv» list_file.append(file) list_file.sort() #Если файлов больше двух, переходим к циклу переноса двух файлов if len(list_file) > 2: for row_file in list_file: if count < 3: #Копируем файл, затем его удаляем из старой директории filename = row_file #Имя файла filedir = path_file #Его текущая директория move_to = save_file #Куда надо перенести shutil.move(os.path.join(filedir, filename), os.path.join(move_to, filename)) count += 1 list_file = [] #Проверяем завершилась выгрузка из БД, переносим оставшиеся файлы и завершаем работу скрипта chek_file = [] for file in os.listdir(path_file): if file.endswith(('_V.txt')): #Файл оканчивается на «_V.txt» chek_file.append(file) if len(chek_file) > 0: chek = 'V' time.sleep(15) for row_file in list_file: #Копирует файл, затем его удаляет из старой директории filename = row_file filedir = path_file move_to = save_file shutil.move(os.path.join(filedir, filename), os.path.join(move_to, filename)) count += 1 print(str(time.ctime()) + ' : Финиш') #Время финиша

В итоге, за два неполных дня автоматически выгружено и перенесено более 500 ГБ, а это 168 файлов в среднем по 3 ГБ (без учета одного обрыва сессии). Применен метод fetchmany() с возможностью установки приемлемого расхода памяти для комфортной работы с другими задачам без зависания системы и ошибок «memory error».

(function () { let cdnUrl = `https://specialsf378ef5-a.akamaihd.net/SelectelBranding/images/` let previousArticleNumber = null let currentArticleNumber = 0 let platform = 'Desktop' let articles = [ { name: 'camera', url: `${cdnUrl}CameraCat`, text: 'умную камеру для\u00A0наблюдения за\u00A0котиками', link: 'https://vc.ru/selectel/306690', num: 3 }, { name: 'chill', url: `${cdnUrl}ChillCat`, text: 'трекер, который подскажет, когда пора отдохнуть', link: 'https://vc.ru/promo/288561-eye-tracker', num: 1 }, { name: 'cloud', url: `${cdnUrl}CloudCat`, text: 'котика: даёшь ему «пять», а\u00A0он делает бэкап в облако', link: 'https://vc.ru/dev/294799-maneki-neko', num: 2 } ] let buttonCycle = document.querySelector('.button--cycle') let buttonChoose = document.querySelector('.button--choose') let buttonMobile = document.querySelector('.button--mobile') let textField = document.querySelector('.selectel-footer-subtitle') let imageAgent = document.querySelector('.image--agent') let banner = document.querySelector('.selectel-footer') buttonCycle.addEventListener('click', cycleClick) buttonChoose.addEventListener('click', () => sendEvent(`Promo ${articles[currentArticleNumber].num} Left`, 'Click')) buttonMobile.addEventListener('click', () => sendEvent(`Promo ${articles[currentArticleNumber].num} Left`, 'Click')) let media = window.matchMedia("(max-width: 570px)") media.addEventListener('change', matchMedia) function matchMedia() { if (media.matches) { platform = 'Mobile' } else { platform = 'Desktop' } update() } matchMedia() function cycleClick(event) { sendEvent(`Promo ${articles[currentArticleNumber].num} Right`, 'Click') if (event) { event.preventDefault() event.stopPropagation() } window.open('https://vc.ru/tag/selectelDIY', '_blank') //cycle(event) } function cycle(event) { // incrementArticleNumber() textField.innerHTML = generatedText() imageAgent.src = articles[currentArticleNumber].url + platform + '.svg?3' imageAgent.setAttribute("class", "") imageAgent.classList.add('image--agent', articles[currentArticleNumber].name) banner.href = articles[currentArticleNumber].link } function update() { banner.href = articles[currentArticleNumber].link imageAgent.src = articles[currentArticleNumber].url + platform + '.svg' textField.innerHTML = generatedText() } function incrementArticleNumber() { previousArticleNumber = currentArticleNumber if (currentArticleNumber >= articles.length - 1) { currentArticleNumber = 0 } else { currentArticleNumber++ } } const sendEvent = (label, action = 'Click') => { const value = `SelectelDIY — loc: Footer — ${label} — ${action}`; if (window.dataLayer !== undefined) { window.dataLayer.push({ event: 'data_event', data_description: value, }); } }; function generatedText() { let defaultText if (platform === 'Desktop') { defaultText = `Мы тут собрали %text%. Хотите научим?` } else { defaultText = `Мы тут собрали %text%.` } return defaultText.replace('%text%', articles[currentArticleNumber].text) } function getRandom(min, max) { min = Math.ceil(min) max = Math.floor(max) return Math.floor(Math.random() * (max - min + 1)) + min } (function create() { currentArticleNumber = getRandom(0, articles.length - 1) cycle() let page = document.querySelector('.page--entry') if (page) { function insertAfter() { let parents = page.querySelectorAll('[data-id="7"]') let referenceNode = parents[0] referenceNode.parentNode.insertBefore(banner, referenceNode.nextSibling); loaded() } setTimeout(() => insertAfter(), 0) } }()) function loaded() { banner.classList.add('loaded') } loadImages([ `${cdnUrl}CameraCatDesktop.svg`, `${cdnUrl}ChillCatDesktop.svg`, `${cdnUrl}CloudCatDesktop.svg`, `${cdnUrl}CameraCatMobile.svg`, `${cdnUrl}ChillCatMobile.svg`, `${cdnUrl}CloudCatMobile.svg?3`, ]) function loadImages(urls) { return Promise.all(urls.map(function (url) { return new Promise(function (resolve) { var img = document.createElement('img'); img.onload = resolve; img.onerror = resolve; img.src = url; }); })); } }())
0
2 комментария
Популярные
По порядку

А чем не устраивал штатный sqlplus? В 12.2 есть корректная выгрузка csv.
После выгрузки таблицы запаковываете ее gz, 40-50 гб займет

1

Для штатной выгрузки на жестком диске не хватало места, поэтому выгружали частями и переносили на ФИР.
Свободного места было около 20 ГБ или чуть больше.

0
Читать все 2 комментария
Что Tele2 предлагает клиентам в «черную пятницу»

На главной распродаже года клиентов компании ждут сразу несколько интересных предложений: скидки на смартфоны, пакеты SMS и безлимитный трафик на YouTube, Яндекс.Карты, Яндекс.Навигатор.

@АнтиспамБот — когда ты реальная заноза в з@днице, или как давали отпор «П0шлым_Дев4енкам»

Прошло меньше двух недель с того дня, как в статье на vc.ru я рассказал про @antispamname_bot, предназначенный для борьбы со спамом в никнейме юзеров телеграмма вида «PEАЛЬНЫЕ_ZНAКОМСТVА» и «ПОИСК_PAPTHЕRОV». В новой статье я поделюсь тем, как прошли эти 10 дней: как бота подключили огромные чаты медиа изданий с аудиторией 100 000+ человек, чаты…

Улучшили лендинг сообщества маркетологов, опросив 80 человек: неочевидный способ использования custdev

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

Хочу кухню как у подруги: зачем в Циан сделали поиск квартир по фото

Рассказывает Юлия Зыкова, руководитель команды «Аудитория» в Циан.

Из науки в IT: как создать свой стартап и стать преподавателем

Как перейти в IT из другой сферы? Как разработать курс, которому нет аналогов? Как студенту получить максимум пользы от занятий? Рассказывает преподаватель OTUS Сергей Окатов, руководитель курсов «Kotlin Backend Developer» и «Kotlin Developer. Basic».

«Я никогда не видел мошенничества такого масштаба»: золотодобыча, которая стоила акционерам $3 млрд Статьи редакции

Проект разработки «крупнейшего золотого месторождения» компанией Bre-X на острове Борнео оказался мошенничеством на миллиарды долларов, за которое так и не смогли никого наказать.

Команда Bre-X на Борнео, около 1997 года, слева направо: менеджер участка Джером Альто, старший вице-президент Джон Фельдерхоф, менеджер по разведке Майкл де Гузман и геолог Сезар Пуспос Сalgary Herald
«Сбермегамаркет» игнорирует претензию клиента

Всем добрый день, господа. Произошла следующая ситуация: являюсь гордым владельцем трёх шерстяных олухов, каждый из которых жалобно пищит, не получая свою вкусняшку. Ранее заказывал в основном Brit с Яндекса, но в этот раз цены там были мягко говоря неадекватные, вследствие чего был вынужден обратить свой взор на сторонние предложение, благо…

Как за 150 тыс. руб. мы увеличили продажи на 67 млн. руб. в месяц

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

5 неожиданных сервисов Почты России, которые постоянно выручают меня в бизнесе

В прошлом году я открыл для себя совершенно удивительные фишки Почты России. С тех пор апка Почты у меня на главном экране в айфоне.

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

По данным Startup Genome, 9 из 10 стартапов терпят неудачу. Возможных причин «смерти» много: недостаточно протестированная гипотеза, неподтвержденная юнит-экономика, неверная стратегия или просто неудача в подходе к продажам.

Кнопка 112 — приложение с определением адреса и большой кнопкой вызова
null