SQL, Анализ данных

Выгрузка SQL c правами на чтение

Время прочтения: 5 мин.

Если выборка небольшая, то все выгрузить поможет оператор IN (WHERE ID IN (список идентификаторов), но что делать если объем данных существенный? Можно вручную писать множество запросов и после их завершения выгружать данные на диск, но это долго. Для решения этой проблемы ИТ специалисты службы Аудита разработали программу на C#, позволяющую автоматизировать процесс выгрузки больших объемов данных с серверов с правами доступа только на чтение.

Про лайфхаки для обработки больших файлов на С# мы уже рассказывали — ссылка

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

Программа автоматически создает SQL запросы с оператором IN по 1000 ID в каждом. Последовательно выполняя запросы записывает результаты в текстовые файлы. По завершению выполнения всех запросов данные объединяются. В функционал Программы заложена возможность одновременного запуска нескольких запросов, их количество указывается в поле «Количество потоков». Программа имеет ограничения в 100 одновременно запущенных потоков. Оператор должен учитывать, что большое количество потоков увеличивает трафик, возможно будет воспринята администраторами как вирусная активность.

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

Существуют две версии программы для серверов MS SQL и Oracle.

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

А вот и долгожданный скрипт.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.Threading;


namespace WindowsFormsApplication4
{
    public delegate void TextDelegate(string str);
    public partial class Form1 : Form
    {
        public string date()
        {
            DateTime dt = DateTime.Now;
            return (dt.ToString());
        }
        int pos;
        int procCount = 0;
        int countList = 0;
        bool ost = false;
        List<string> id_list = new List<string>();
        List<string> sql_plus = new List<string>();
        List<string> id_upd = new List<string>(System.IO.File.ReadAllLines(@"ID_UPD.txt"));
        string SQL = System.IO.File.ReadAllText(@"SQL.txt");
        List<string> login = new List<string>(System.IO.File.ReadAllLines("setting.ini"));
        public Process Proc;
        public Form1()
        {
            InitializeComponent();
        }

        private void AddText(string str)
        {
            richTextBox1.Text += str;
        }

        private void Skleyka()
        {
            Proc = new Process();
            Proc.StartInfo.FileName = "SKLEYKA.vbs";
            Proc.Start();
            Proc.WaitForExit();
            this.Invoke((MethodInvoker)delegate ()
            {
                richTextBox1.AppendText(date() + "  Выгрузка выполнена!" + Environment.NewLine);
            });
        }

        private void ExternalProcess()
        {
            Proc = new Process();
            Proc.StartInfo.CreateNoWindow = true;
            Proc.StartInfo.UseShellExecute = false;
            Proc.StartInfo.RedirectStandardOutput = true;
            Proc.StartInfo.FileName = "DEL.bat";
            Proc.Start();
            var buf = new char[256];
            while (!Proc.HasExited)
            {
                var len = Proc.StandardOutput.Read(buf, 0, buf.Length);
                var str = new string(buf, 0, len);
                System.Threading.Thread.Sleep(10);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if ((Convert.ToInt32(textBox1.Text) < 100) && (Convert.ToInt32(textBox1.Text) > 0))
            {
                Thread p1 = new Thread(load);
                p1.IsBackground = true;
                p1.Start();
            }
            else
            {
                MessageBox.Show("Количество портов 1-100.");
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            timer1.Enabled = false;
            System.IO.Directory.CreateDirectory("SQL");
            System.IO.Directory.CreateDirectory("REZULT");
            richTextBox1.AppendText(date() + "   DELETE FILES!" + Environment.NewLine);
            Thread Thr = new Thread(ExternalProcess);
            Thr.IsBackground = true;
            Thr.Start();
        }

        public void load()
        {
            countList = (id_upd.Count / 1000);
            if (id_upd.Count % 1000 == 0)
            {
                ost = false;
            }
            else
            {
                ost = true;
            }

            for (int i = 0; i < countList; i++)
            {

                string s = "";
                for (int j = i * 1000; j < (i + 1) * 1000; j++)
                {
                    s = s + "'" + id_upd[j] + "'" + Environment.NewLine + ",";
                }
                id_list.Add(string.Format(SQL, i.ToString(), s.Substring(0, s.Length - 1)));
                this.Invoke((MethodInvoker)delegate ()
                {
                    progressBar1.Maximum = countList;
                    progressBar1.PerformStep();
                });
            }

            if (ost == true)
            {
                string s = "";
                for (int j = (countList * 1000) + 0; j < (countList * 1000) + (id_upd.Count % 1000); j++)
                {
                    s = s + "'" + id_upd[j] + "'" + Environment.NewLine + ",";
                }
                id_list.Add(string.Format(SQL, countList.ToString(), s.Substring(0, s.Length - 1)));
            }
            save();
        }

        public void save()
        {
            for (int i = 0; i < id_list.Count; i++)
            {
                System.IO.File.WriteAllText(@"SQL\SQL_" + i.ToString() + ".sql", id_list[i]);
            }
            this.Invoke((MethodInvoker)delegate ()
            {
                richTextBox1.AppendText(date() + "  DATA UPLOADED!" + Environment.NewLine);
            });
            loadSql(countList);
        }

        void loadSql(int count)
        {
            for (int i = 0; i <= count; i++)
            {
                sql_plus.Add(@"sqlplus " + login[0] + "/" + login[1] + "@" + login[2] + " @SQL/SQL_" + i.ToString() + ".sql");
            }
            this.Invoke((MethodInvoker)delegate ()
            {
                timer1.Interval = 10;
                pos = 0;
                timer1.Enabled = true;
            });
        }

        void PLoad(int num)
        {
            pos++;
            procCount = procCount + 1;
            Process p1;
            p1 = new Process();
            p1.StartInfo.CreateNoWindow = true;
            p1.StartInfo.UseShellExecute = false;
            p1.StartInfo.FileName = "cmd";
            p1.StartInfo.Arguments = "/c " + sql_plus[num];
            p1.Start();
            p1.WaitForExit();
            this.Invoke((MethodInvoker)delegate ()
            {
                richTextBox1.AppendText(date() + "  " + sql_plus[num].Remove(0, sql_plus[num].IndexOf("SQL_")) + Environment.NewLine);
                progressBar2.Maximum = sql_plus.Count;
                progressBar2.Step = 1;
                progressBar2.PerformStep();
            });
            procCount = procCount - 1;
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            if (pos < sql_plus.Count)
            {
                if (procCount < Convert.ToInt32(textBox1.Text))
                {
                    Thread t = new Thread(delegate ()
                    {
                        PLoad(pos);
                    }
                    );
                    t.IsBackground = true;
                    t.Start();
                }
            }
            else
            {
                timer1.Enabled = false;
                Thread t = new Thread(delegate ()
                {
                    rezult();
                }
                    );
                t.IsBackground = true;
                t.Start();
            }
        }


        void rezult()
        {
            int sqlCount = 0;
            int rezultCount = 0;
            sqlCount = new System.IO.DirectoryInfo(@"SQL").GetFiles().Length;
            rezultCount = new System.IO.DirectoryInfo(@"REZULT").GetFiles().Length;
            this.Invoke((MethodInvoker)delegate ()
            {
                richTextBox1.AppendText(date() + "  RESUMING!" + Environment.NewLine);
            });
            while (sqlCount != rezultCount)
            {
                for (int i = 0; i < sqlCount; i++)
                {
                    if (procCount < Convert.ToInt32(textBox1.Text))
                    {
                        if (!System.IO.File.Exists("REZULT/REZULT_" + i + ".txt"))
                        {
                            PLoad(i);
                        }
                    }
                }
                rezultCount = new System.IO.DirectoryInfo(@"REZULT").GetFiles().Length;
            }

            while (procCount != 0)
            {
                System.Threading.Thread.Sleep(500);
            }
            Thread t = new Thread(delegate ()
            {
                Skleyka();
            }
                    );
            t.IsBackground = true;
            t.Start();           
        }
    }
}
Советуем почитать