예제 #1
0
        static void TestTpcds_LoadData()
        {
            var files = Directory.GetFiles(@"../../../tpcds", "*.sql");

            string[] norun = { "q1", "q10" };

            Tpcds.CreateTables();
            Tpcds.LoadTables("tiny");
            Tpcds.AnalyzeTables();

            // make sure all queries can generate phase one opt plan
            QueryOption option = new QueryOption();

            option.optimize_.enable_subquery_unnest_ = true;
            option.optimize_.remove_from_            = false;
            option.optimize_.use_memo_ = false;
            foreach (var v in files)
            {
                char[] splits = { '.', '/', '\\' };
                var    tokens = v.Split(splits, StringSplitOptions.RemoveEmptyEntries);

                if (norun.Contains(tokens[1]))
                {
                    continue;
                }

                var sql    = File.ReadAllText(v);
                var result = SQLStatement.ExecSQL(sql, out string phyplan, out _, option);
            }
        }
예제 #2
0
        static void RunSQLFromFile(string filename)
        {
            // Not working as expected, need to investigate.
            var option = new QueryOption();

            option.explain_.mode_         = ExplainMode.full;
            option.optimize_.use_memo_    = true;
            option.explain_.show_estCost_ = false;

            string allquery = File.ReadAllText(filename);

            string[] listquery = allquery.Split(';');

            List <string> listoutput = new List <string>();
            int           linenum    = 0;

            for (int i = 0; i < listquery.Length; ++i)
            {
                linenum = i + 1;
                string sql = listquery[i].Trim();
                if (sql.Length <= 0)
                {
                    continue;
                }
                else if (sql.StartsWith("--"))
                {
                    continue;
                }
                try
                {
                    Console.WriteLine(sql);

                    string outline = linenum.ToString();
                    outline += ": " + sql + "\n";
                    listoutput.Add(outline);
                    var result = SQLStatement.ExecSQL(sql, out string physicplan, out string error_, option);
                    if (physicplan != null)
                    {
                        Console.WriteLine(physicplan);
                        listoutput.Add(physicplan);
                    }

                    if (result != null)
                    {
                        Console.WriteLine(result);
                        listoutput.Add(result.ToString());
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("SQL: " + sql + "\nEXCEPTION: " + e + "\n");
                    continue;
                }
            }
            string alloutput = string.Join('\n', listoutput);
            string outfile   = filename + ".out";

            File.WriteAllText(outfile, alloutput);
        }
예제 #3
0
 static public void AnalyzeTables()
 {
     foreach (var v in tabnames_)
     {
         var sql = $"analyze {v};";
         SQLStatement.ExecSQL(sql, out _, out _);
     }
 }
예제 #4
0
파일: Catalog.cs 프로젝트: pkommoju/qpmodel
        static void createBuildInTestTables()
        {
            // create tables
            string[] createtables =
            {
                @"create table test (t1 int, t2 int, t3 int, t4 int);"
                ,
                @"create table a (a1 int, a2 int, a3 int, a4 int);",
                @"create table b (b1 int, b2 int, b3 int, b4 int);",
                @"create table c (c1 int, c2 int, c3 int, c4 int);",
                @"create table d (d1 int, d2 int, d3 int, d4 int);",
                // nullable tables
                @"create table r (r1 int, r2 int, r3 int, r4 int);",
                // distributed tables
                @"create table ad (a1 int, a2 int, a3 int, a4 int) distributed by a1;",
                @"create table bd (b1 int, b2 int, b3 int, b4 int) distributed by b1;",
                @"create table cd (c1 int, c2 int, c3 int, c4 int) distributed by c1;",
                @"create table dd (d1 int, d2 int, d3 int, d4 int) distributed by d1;",
                @"create table ar (a1 int, a2 int, a3 int, a4 int) replicated;",
                @"create table br (b1 int, b2 int, b3 int, b4 int) replicated;",
                @"create table arb (a1 int, a2 int, a3 int, a4 int) roundrobin;",
                @"create table brb (b1 int, b2 int, b3 int, b4 int) roundrobin;",
                // steaming tables
                @"create table ast (a0 datetime, a1 int, a2 int, a3 int, a4 int);",     // bounded table with ts
                @"create stream ainf (a0 datetime, a1 int, a2 int, a3 int, a4 int);",   // unbounded table
            };
            SQLStatement.ExecSQLList(string.Join("", createtables));

            // load tables
            var appbin_dir = AppContext.BaseDirectory.Substring(0, AppContext.BaseDirectory.LastIndexOf("bin"));
            var folder     = $@"{appbin_dir}/../data";
            var tables     = new List <string>()
            {
                "test", "a", "b", "c", "d", "r", "ad", "bd", "cd", "dd", "ar", "br", "arb", "brb", "ast"
            };

            foreach (var v in tables)
            {
                string filename = $@"'{folder}/{v}.tbl'";
                var    sql      = $"copy {v} from {filename};";
                var    result   = SQLStatement.ExecSQL(sql, out _, out _);
            }

            // create index
            string[] createindexes =
            {
                @"create unique index dd1 on d(d1);",
                @"create index dd2 on d(d2);",
            };
            SQLStatement.ExecSQLList(string.Join("", createindexes));

            // analyze tables
            foreach (var v in tables)
            {
                var sql    = $"analyze {v};";
                var result = SQLStatement.ExecSQL(sql, out _, out _);
            }
        }
예제 #5
0
        static public void LoadTables(string subfolder)
        {
            string curdir = Directory.GetCurrentDirectory();
            string folder = $@"{curdir}\..\..\..\tpch\data\{subfolder}";

            foreach (var v in tabnames_)
            {
                string filename = $@"'{folder}\{v}.tbl'";
                var    sql      = $"copy {v} from {filename};";
                SQLStatement.ExecSQL(sql, out _, out _);
            }
        }
예제 #6
0
        static public void CreateTables()
        {
            // hack: drop tpch table customer
            SQLStatement.ExecSQL("drop table customer;", out _, out _);

            string curdir   = Directory.GetCurrentDirectory();
            string folder   = $@"{curdir}\..\..\..\tpch\create";
            string filename = $@"{folder}\tpch.sql";
            var    sql      = File.ReadAllText(filename);

            SQLStatement.ExecSQLList(sql);
        }
예제 #7
0
        static void createBuildInTestTables()
        {
            // create tables
            string[] createtables =
            {
                @"create table test (a1 int, a2 int, a3 int, a4 int);"
                ,
                @"create table a (a1 int, a2 int, a3 int, a4 int);",
                @"create table b (b1 int, b2 int, b3 int, b4 int);",
                @"create table c (c1 int, c2 int, c3 int, c4 int);",
                @"create table d (d1 int, d2 int, d3 int, d4 int);",
                // nullable tables
                @"create table r (r1 int, r2 int, r3 int, r4 int);",
                // distributed tables
                @"create table ad (a1 int, a2 int, a3 int, a4 int) distributed by a1;",
                @"create table bd (b1 int, b2 int, b3 int, b4 int) distributed by b1;",
                @"create table cd (c1 int, c2 int, c3 int, c4 int) distributed by c1;",
                @"create table dd (d1 int, d2 int, d3 int, d4 int) distributed by d1;",
                // steaming tables
                @"create table ast (a0 datetime, a1 int, a2 int, a3 int, a4 int);",
            };
            SQLStatement.ExecSQLList(string.Join("", createtables));

            // load tables
            var curdir = Directory.GetCurrentDirectory();
            var folder = $@"{curdir}\..\..\..\data";
            var tables = new List <string>()
            {
                "a", "b", "c", "d", "r", "ad", "bd", "cd", "dd", "ast"
            };

            foreach (var v in tables)
            {
                string filename = $@"'{folder}\{v}.tbl'";
                var    sql      = $"copy {v} from {filename};";
                var    result   = SQLStatement.ExecSQL(sql, out _, out _);
            }

            // create index
            string[] createindexes =
            {
                @"create unique index dd1 on d(d1);",
                @"create index dd2 on d(d2);",
            };
            SQLStatement.ExecSQLList(string.Join("", createindexes));

            // analyze tables
            foreach (var v in tables)
            {
                var sql    = $"analyze {v};";
                var result = SQLStatement.ExecSQL(sql, out _, out _);
            }
        }
예제 #8
0
        static void TestDataSet2()
        {
            Random rand = new Random();

            int inside(int d)
            {
                var x   = rand.NextDouble();
                var y   = rand.NextDouble();
                var ret = x * x + y * y <= 1 ? 1 : 0;

                return(ret);
            }

            SQLContext sqlContext = new SQLContext();

            SQLContext.Register <int, int>("inside", inside);
            var sql  = "SELECT 4.0*sum(inside(a1.a1))/count(*) from a a1, a a2, a a3, a a4, a a5, a a6, a a7, a a8, a a9, a a10";
            var rows = SQLStatement.ExecSQL(sql, out string plan, out _);
        }
예제 #9
0
        static void TestDataSet()
        {
            // register c#'s sqrt as an external function
            string sqroot(double d) => Math.Sqrt(d).ToString("#.###");

            SQLContext sqlContext = new SQLContext();

            SQLContext.Register <double, string>("sqroot", sqroot);
            var a = sqlContext.Read("a");
            var b = sqlContext.Read("b");

            a.filter("a1>1").join(b, "b2=a2").select("a1", "sqroot(b1*a1+2)").show();
            string s = a.physicPlan_.Explain();

            Console.WriteLine(s);

            var sql  = "SELECT a1, sqroot(b1*a1+2) from a join b on b2=a2 where a1>1";
            var rows = SQLStatement.ExecSQL(sql, out string plan, out _);
        }
예제 #10
0
        static void TestJobench()
        {
            var files = Directory.GetFiles(@"../../../jobench");

            JOBench.CreateTables();

            // make sure all queries can generate phase one opt plan
            QueryOption option = new QueryOption();

            option.optimize_.TurnOnAllOptimizations();
            option.optimize_.memo_use_joinorder_solver_ = true;
            foreach (var v in files)
            {
                var sql    = File.ReadAllText(v);
                var result = SQLStatement.ExecSQL(sql, out string phyplan, out _, option);
                Debug.Assert(result != null);
                Debug.Assert(phyplan != null);
                Console.WriteLine(v);
                Console.WriteLine(phyplan);
            }
        }