免费注册
帮助文档(华北一、二)

  • 1. DW客户端的方式访问

    ● 1.1 DW(greenplum)客户端方式访问(以Centos为例)

    如果你选择的数据仓库类型是greenplum、可以采用下面的方式访问

    1)下载greenplum客户端解压

     wget http://downloadupt.infile.inspurcloud.cn/greenplum-client.tar.gz

    tar -zxvf greenplum-client.tar.gz

    2)配置dw客户端

    进入greenplum-client安装目录,编辑 greenplumclientpath.sh 修改UDWHOME:export UDWHOME= client安装目录(如/root/greenplum-client)

    3) 使配置生效

    在~/.bashrc中添加如下配置

    source /data/greenplum-client/greenplumclientpath.sh

    source ~/.bashrc

    备注:/data/greenplum-client是greenplum-client的安装路径

    4) 连接数据库

    psql -h hostIP(或域名) –U username -d database -p port –W

    ● 1.2 udw(udpg)客户端方式访问(以Centos为例)

    如果你选择的数据仓库类型是udpg、可以采用下面的方式访问

    1)下载dw客户端

     wget http://udwclient.ufile.ucloud.cn/udw-client.tar

    解压: tar xvf udw-client.tar

    2)配置udw客户端

    进入udw-client安装目录,编辑 udwclientpath.sh,修改UDWCLIENT:export UDWCLIENT= client安装目录(如/root/udw-client)

    3)使配置生效在~/.bashrc中添加如下配置

     
    source /data/udw-client/udw_client_path.sh 
    source ~/.bashrc 
    备注:/data/udw-client是udw-client的安装路径

    4) 连接数据库

    psql -h hostIP(或域名) –U username -d database -p port –W

    2. python客户端访问

     $yum install python-psycopg2

    ● 示例1. 连接DW testconn.py

     
    #!/usr/bin/python   
    import psycopg2 
    conn = psycopg2.connect(database="dev", user="username", 
    password="password", host="hostIP", port="port") 
    print "Opened database successfully"

    执行 python testconn.py

    ● 示例2. 创建一个表 createTable.py

     
    #!/usr/bin/python   
    import psycopg2 
    conn = psycopg2.connect(database="dev", user="username", 
    password="password", host="hostIP", port="port") 
    print "Opened database successfully" 
    cur = conn.cursor() 
    cur.execute('''CREATE TABLE COMPANY     
    (ID INT PRIMARY KEY     NOT NULL,     
    NAME           TEXT    NOT NULL,     
    AGE            INT     NOT NULL,     
    ADDRESS        CHAR(50),     
    SALARY         REAL);''') 
    print "Table created successfully" 
    conn.commit() 
    conn.close()

    ● 示例3. 插入记录 insert.py

     
    #!/usr/bin/python   
    import psycopg2 
    conn = psycopg2.connect(database="dev", user="username", 
    password="password", host="hostIP", port="port") 
    print "Opened database successfully" 
    cur = conn.cursor() 
    cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \   
    VALUES (1, 'Paul', 32, 'California', 20000.00 )"); 
    cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \   
    VALUES (2, 'Allen', 25, 'Texas', 15000.00 )"); 
    conn.commit() 
    print "Records created successfully"; 
    conn.close()

    ● 示例4. 查询 select.py

     
    #!/usr/bin/python   
    import psycopg2 conn = psycopg2.connect(database="dev", user="username", 
    password="password", host="hostIP", port="port") 
    print "Opened database successfully" 
    cur = conn.cursor() 
    cur.execute("SELECT id, name, address, salary  from COMPANY") 
    rows = cur.fetchall() 
    for row in rows:     
      print "ID = ", row[0]     
      print "NAME = ", row[1]     
      print "ADDRESS = ", row[2]     
      print "SALARY = ", row[3], "\n" 
    print "Operation done successfully"; 
    conn.close() 

    ● 示例5. 更新 update.py

     
    #!/usr/bin/python   
    import psycopg2 
    conn = psycopg2.connect(database="dev", user="username", 
    password="password", host="hostIP", port="port") 
    print "Opened database successfully" 
    cur = conn.cursor() 
    cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1") 
    conn.commit 
    print "Total number of rows updated :", cur.rowcount 
    cur.execute("SELECT id, name, address, salary  from COMPANY") 
    rows = cur.fetchall() 
    for row in rows:    
      print "ID = ", row[0]    
      print "NAME = ", row[1]    
      print "ADDRESS = ", row[2]    
      print "SALARY = ", row[3], "\n" 
    print "Operation done successfully"; 
    conn.close() 

    ● 示例6. 删除 delete.py

     
    #!/usr/bin/python   
    import psycopg2 conn = psycopg2.connect(database="dev", user="username", 
    password="password", host="hostIP", port="port") 
    print "Opened database successfully" 
    cur = conn.cursor() cur.execute("DELETE from COMPANY where ID=2;") 
    conn.commit 
    print "Total number of rows deleted :", cur.rowcount 
    cur.execute("SELECT id, name, address, salary  from COMPANY") 
    rows = cur.fetchall() 
    for row in rows:     
      print "ID = ", row[0]     
      print "NAME = ", row[1]     
      print "ADDRESS = ", row[2]     
      print "SALARY = ", row[3], "\n" 
    print "Operation done successfully"; 
    conn.close() 

    3. php客户端

     yum install php-pgsql

    示例1. 连接 conn.php

     
    <?php 
    $host        = "host=hostIP"; 
    $port        = "port=port"; 
    $dbname      = "dbname=dbname"; 
    $credentials = "user=user password=password"; 
    $db = pg_connect( "$host $port $dbname $credentials"  ); 
    if(!$db){      
        echo "Error : Unable to open database\n"; 
    else {   
      echo "Opened database successfully\n";
    ?>

    ● 示例2. 创建表 create.php

     
    <?php 
    $host        = "host=hostIP"; 
    $port        = "port=port"; 
    $dbname      = "dbname=dbname"; 
    $credentials = "user=user password=password"; 
    $db = pg_connect( "$host $port $dbname $credentials"  ); 
    if(!$db){     
      echo "Error : Unable to open database\n"; 
    } else {     
      echo "Opened database successfully\n"; 
    $sql =<<<EOF   
      CREATE TABLE COMPANY   
      (ID INT PRIMARY KEY NOT NULL,    
      NAME TEXT NOT NULL,    
      AGE INT NOT NULL,    
      ADDRESS CHAR(50),    
      SALARY REAL); 
    EOF; 
    $ret = pg_query($db, $sql); 
    if(!$ret) 
      { echo pg_last_error($db);         
        } else {           
          echo "Table created successfullyn";         
        } 
    pg_close($db); 
    ?>

    ● 示例3. 插入 insert.php

     
    <?php 
    $host        = "host=hostIP"; 
    $port        = "port=port"; 
    $dbname      = "dbname=dbname"; 
    $credentials = "user=user password=password"; 
    $db = pg_connect( "$host $port $dbname $credentials"  ); 
    if(!$db){    
      echo "Error : Unable to open database\n"; 
    } else {    
      echo "Opened database successfully\n"; 
    $sql =<<<EOF    
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)    
      VALUES (1, 'Paul', 32, 'California', 20000.00 );    
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)    
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); 
    EOF; 
    $ret = pg_query($db, $sql); 
    if(!$ret){    
      echo pg_last_error($db); 
    } else {    
      echo "Records created successfully\n"; 
    pg_close($db); 
    ?>

    ● 示例4. 查询 select.php

     
    <?php 
    $host        = "host=hostIP"; 
    $port        = "port=port"; 
    $dbname      = "dbname=dbname"; 
    $credentials = "user=user password=password"; 
    $db = pg_connect( "$host $port $dbname $credentials"  ); 
    if(!$db){    
      echo "Error : Unable to open database\n"; 
    } else {    
      echo "Opened database successfully\n"; 
    $sql =<<<EOF    
      SELECT * from COMPANY; 
    EOF; 
    $ret = pg_query($db, $sql); 
    if(!$ret){    
      echo pg_last_error($db);    
      exit; 
    while($row = pg_fetch_row($ret)){    
      echo "ID = ". $row[0] . "\n";    
      echo "NAME = ". $row[1] ."\n";    
      echo "ADDRESS = ". $row[2] ."\n";    
      echo "SALARY =  ".$row[4] ."\n\n"; 
    echo "Operation done successfully\n"; 
    pg_close($db); 
    ?>

    ● 示例5. 更新 update.php

     
    <?php 
    $host        = "host=hostIP"; 
    $port        = "port=port"; 
    $dbname      = "dbname=dbname"; 
    $credentials = "user=user password=password"; 
    $db = pg_connect( "$host $port $dbname $credentials"  ); 
    if(!$db){    
      echo "Error : Unable to open database\n"; 
    } else {    
      echo "Opened database successfully\n"; 
    $sql =<<<EOF    
      SELECT * from COMPANY; 
    EOF; 
    $ret = pg_query($db, $sql); 
    if(!$ret){    
      echo pg_last_error($db);    
      exit; 
    while($row = pg_fetch_row($ret)){    
      echo "ID = ". $row[0] . "\n";    
      echo "NAME = ". $row[1] ."\n";    
      echo "ADDRESS = ". $row[2] ."\n";    
      echo "SALARY =  ".$row[4] ."\n\n"; 
    echo "Operation done successfully\n"; 
    pg_close($db); 
    ?>

    ● 示例6. 删除 delete.php

     
    <?php 
    $host        = "host=hostIP"; 
    $port        = "port=port"; 
    $dbname      = "dbname=dbname"; 
    $credentials = "user=user password=password"; 
    $db = pg_connect( "$host $port $dbname $credentials"  ); 
    if(!$db){    
      echo "Error : Unable to open database\n"; 
    } else {    
      echo "Opened database successfully\n"; 
    $sql =<<<EOF    
      DELETE from COMPANY where ID=2; 
    EOF; 
    $ret = pg_query($db, $sql); 
    if(!$ret){    
      echo pg_last_error($db);    
      exit; 
    } else {    
      echo "Record deleted successfully\n"; 
    $sql =<<<EOF    
      SELECT * from COMPANY; 
    EOF; 
    $ret = pg_query($db, $sql); 
    if(!$ret){    
      echo pg_last_error($db);    
      exit; 
    }
    while($row = pg_fetch_row($ret)){    
      echo "ID = ". $row[0] . "\n";    
      echo "NAME = ". $row[1] ."\n";    
      echo "ADDRESS = ". $row[2] ."\n";    
      echo "SALARY =  ".$row[4] ."\n\n"; 
    echo "Operation done successfully\n"; 
    pg_close($db); 
    ?>

    4. SQL Workbench/J 访问 dw

    除了以上几种方式,DW还可以使用SQL Workbench/J来进行访问,详情可见:SQL Workbench/J访问DW


文档是否已解决您的问题?

  已解决   未解决

如您有其它疑问,您也可以与我们技术专家联系探讨。

联系技术专家