首页手记 PostgreSQL和Greenplum、Npgsql

PostgreSQL和Greenplum、Npgsql

标签：

大数据

想着要不要写，两个原因“懒”和“空”。其实懒和空也是有联系的，不是因为懒的写，而是因为对PostgreSQL和Npgsql的知识了解匮乏，也就懒得写。好了，开头就写到这里，有些绕口令的感觉。一贯以这种不靠谱的描述开头，也成为了一种习惯，既然是习惯，也还不算坏，得坚持。

　　其实想写PostgreSQL和Npgsql的博客起因还是因为项目中用到了，虽然网上有很多对PostgreSQL数据库支持的类库，可以拿来就用，但多少有些不踏实。也许是老了的原因，危机意识的督促下，还是决定对PostgreSQL数据库和Npgsql进行一番了解，且听我道来。

　　一、PostgreSQL数据库

　　介绍：PostgreSQL是一种运行在Unix和Linux操作系统(在NT平台借助Cygnus也可以运行)平台上的免费的开放源码的关系数据库。最早是由美国加州大学伯克利分校开发的，开始只是作为一个演示系统发表，但是随着时间的推移，逐步分发，得到很多实际的应用，才逐步流行起来。

　　网址：https://www.postgresql.org/

　　特点：1.省钱，可以运行在Unix和Lunux操作系统上，Windows Server 什么时候也能高风亮节回。

　　　　　2.支持SQL。

　　　　　3.有丰富的数据类型。许多数据类型是一些商业数据库都没有提供的。

　　　　　4.面向对象，它包含了一些面向对象的技术，如继承和类。

　　　　　5.支持大数据，它不同于一般的桌面数据库，能够支持几乎不受限制大小的数据库，而且性能稳定。

　　　　　　描述：我想这个特点也是觉大多数据考虑使用PostgreSQL数据库的原因之一，当然这种场景应该是有要求的，比如一些并发不高，但涉及统计分析类业务的场景相对比较适合。

　　　　　6.方便集成web，提供一些接口方便 PHP，Perl等语言操作数据库。

　　　　　　描述：重点来了，Npgsql动态库就是为了更好的支持C#的一个强大的类库(后面会有描述)。

　　　　　7.事务处理。相对一些其他免费数据库如MySQL，他提供了事务处理，可以满足一些商业领域的数据需要。

　　　　　　描述：事务对数据库来真的是太重要了，所以PostgreSQL不会遗忘。

　　　　　8.PostgreSQL运行速度明显低于MySQL。因为MySQL使用了线程，而PostgreSQL使用的是进程。在不同线程之间的环境转换和访问公用的存储区域显然要比在不同的进程之间要快得多。

　　　　　9.PostgreSQL的Sql语法相对更加干净和干练(这个特点的总结来自于公司PostgreSQL专家的现场采访)

　　二、Greenplum

　　介绍：Greenplum是一家总部位于美国加利福尼亚州，为全球大型企业用户提供新型企业级数据仓库(EDW)、企业级数据云(EDC)和商务智能(BI)提供解决方案和咨询服务

　　网址：http://www.oschina.net/p/greenplum(开源社区介绍及引导页)

　特点：1.大规模并行处理架构

　　　　　2.高性能加载，使用 MPP 技术，提供 Petabyte 级别数据量的加载性能

　　　　　3.大数据工作流查询优化

　　　　　4.多态数据存储和执行

　　　　　5.基于 Apache MADLib 的高级机器学习功能

　　应用场景：1.大数据量的统计分析类业务（这个也是目前统计分析业务结合考量后所出的选择）。

　　三、Npgsql

　　介绍：Npgsql是PostgreSQL的一个.NET数据提供程序。

　　网址：http://www.npgsql.org/

　　四、.net 调用Greenplum的实战

　　在最近的一个项目中，由于业务的需要。业务特点：大数据统计分析业务，并发不高，但数据量相对巨大。为此DBA方面决定尝试Greenplum数据库进行相关业务数据层面的开发，所谓数据层面的开发，你懂得，就是将数据处理业务写在如存储过程中(SqlServer 数据库)或者函数(Greenplum数据库)。

　　1.Greenplum函数

　　在SqlServer数据库中，一般业务的封装都是以存储过程的形式存在的。但在Greenplum中则是以函数的方式进行业务封装的。正是由于这种两不同的封装方式导致我们调用或者说对接的方式不同。

(1).SqlServer 存储过程的调用方式
DataSet ds = MSSqlHelper.ExecuteDataset(DataBase.Sql_TKAgentDB, CommandType.StoredProcedure, "[prm].[WCall_GetCostEffect]", parms);
SqlServer 存储过程的调用方式我相信大家都已经很熟悉了，上面代码事例中的 parms 是SqlParameter 数组，在此我就不再进行过多描述了，其实我是希望以对比的方式来看看 PostgresSQL中的函数调用方式有什么不同。

(2).PostgresSQL中函数的调用方式

SELECT * FROM app.test
                                (
                                    @aIn,
                                    @bIn,
                                    @cIn,
                                    @dIn,
                                    @eIn
                                )
                                as (
                                aOut varchar ,
                                bOut varchar ,
                                cOut integer,
                                dOut numeric(16,2),
                                eOut numeric(16,2),
                                fOut numeric(16,2),
                                error_info varchar);
                                "

大家看到区别了吧，PostgresSql中函数的调用是以 SELECT * FROM 函数(app.test)的方式进行调用，同时要注意的是PostgresSql的输出参数是要声明类型的，比如

aOut integer，aOut 是就是返回值之一，在此声明为整形类型。之所以要强调一下是因为在实际开发当中很容易以SqlServer 存储过程调用方式的思维所左右，导致很长一段时间内不知道问题究竟是出在哪里。下面我在描述一个完整的方法来整体看一PostgresSql函数的调用方式。

string sql = @"SELECT * FROM app.test
                                (
                                    @aIn,
                                    @bIn,
                                    @cIn,
                                    @dIn,
                                    @eIn
                                )
                                as (
                                aOut varchar ,
                                bOut varchar ,
                                cOut integer,
                                dOut numeric(16,2),
                                eOut numeric(16,2),
                                fOut numeric(16,2),
                                error_info varchar);
                                ";

                NpgsqlParameter[] parms = new NpgsqlParameter[] {
                    new NpgsqlParameter("@aIn",NpgsqlDbType.Integer) { Value=reconciliatioQuery.SupplierID!=-1?reconciliatioQuery.SupplierID:null},
                    new NpgsqlParameter("@bIn",NpgsqlDbType.Integer) { Value =reconciliatioQuery.BillPeriod },
                    new NpgsqlParameter("@cIn",NpgsqlDbType.Integer) {Value = reconciliatioQuery.PinAccStatID },
                    new NpgsqlParameter("@eIn",NpgsqlDbType.Varchar) { Value=reconciliatioQuery.EmployeeNum},
                    new NpgsqlParameter("@fIn",NpgsqlDbType.Varchar) { Value=reconciliatioQuery.IP}
                };

                DataSet ds = PostgreSqlHelper.ExecuteQuery(this.ConnectionString, CommandType.Text, sql, parms);
                if (ds != null && ds.Tables != null)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                        errMessage = DataHelper.GetString(ds.Tables[0].Rows[0]["error_info"]);

                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }

这个代码片段相对清晰的说明了问题。NpgsqlParameter 对象是 Npgsql 动态库中提供的一个对象，大家只要引用了Npgsql.dll 就可以了。至于其他的参数我想就不用我过多的解释了，相信大家一看就明白了，由于是实际业务中的代码片段，在此就不进行方法功能的描述了。

　　五、PostgreSQLHelper类

　　你也知道，Mysql的支持类我们有MySqlHelper支持类的封装，SqlServer的支持类我们有MsSqlHelper的支持类的封装，所以PostgresSqlHelper的支持类怎么能缺席，当然以下代码是我从网上下载到的，之所以在此列出，一方面是希望整个博客的内容更加完整化，体系化，同时也是希望能做个内容的备份，便于以后自己再用到这块内的时候有个引导的作用。当然也希望能给大家带来直接的便利。

public class PostgreSQL
    {
        /// <summary>  
        /// 得到数据条数  
        /// </summary>  
        public int GetCount(string connectionString, string tblName, string condition)
        {
            StringBuilder sql = new StringBuilder("select count(*) from " + tblName);
            if (!string.IsNullOrEmpty(condition))
                sql.Append(" where " + condition);

            object count = ExecuteScalar(connectionString, CommandType.Text, sql.ToString(), null);
            return int.Parse(count.ToString());
        }

        /// <summary>  
        /// 执行查询，返回DataSet  
        /// </summary>  
        public DataSet ExecuteQuery(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        return ds;
                    }
                }
            }
        }

        /// <summary>  
        /// 在事务中执行查询，返回DataSet  
        /// </summary>  
        public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            NpgsqlCommand cmd = new NpgsqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "ds");
            cmd.Parameters.Clear();
            return ds;
        }

        /// <summary>  
        /// 执行 Transact-SQL 语句并返回受影响的行数。  
        /// </summary>  
        public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            NpgsqlCommand cmd = new NpgsqlCommand();

            using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>  
        /// 在事务中执行 Transact-SQL 语句并返回受影响的行数。  
        /// </summary>  
        public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            NpgsqlCommand cmd = new NpgsqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>  
        /// 执行查询，返回DataReader  
        /// </summary>  
        public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            NpgsqlCommand cmd = new NpgsqlCommand();
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);

            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>  
        /// 在事务中执行查询，返回DataReader  
        /// </summary>  
        public DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            NpgsqlCommand cmd = new NpgsqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;
        }

        /// <summary>  
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。  
        /// </summary>  
        public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            NpgsqlCommand cmd = new NpgsqlCommand();

            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>  
        /// 在事务中执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。  
        /// </summary>  
        public object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            NpgsqlCommand cmd = new NpgsqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>  
        /// 生成要执行的命令  
        /// </summary>  
        /// <remarks>参数的格式：冒号+参数名</remarks>  
        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType,
            string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText.Replace("@", ":").Replace("?", ":").Replace("[", "\"").Replace("]", "\"");

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (NpgsqlParameter parm in cmdParms)
                {
                    parm.ParameterName = parm.ParameterName.Replace("@", ":").Replace("?", ":");

                    cmd.Parameters.Add(parm);
                }
            }
        }

　　PostgresSql、Greenplum和Npgsql 就写到这里，由于本人才疏学浅，加之对PostgresSql、Greenplum的了解都不算太深入，所以只做皮毛层面的讲解，希望能对大家有一定的帮助。其中实际部分的内容相对来说是比较重要的干货，大家在实际开发中如果遇到什么问题，可以通过留言的方式进行沟通。

　　感谢大家的阅读，我是百灵，就写到这里。再回。

点击查看更多内容