现场环境：Oracle 11g 64bit linux edition
Greenplum Pivotal HD HAWQ 1.0
操作系统 RHEL 6.3 x86_64
为了实现在Oracle中直接访问HAWQ中的数据，需要首先安装Oracle Gateway 11g x86_64 linux edition
需要安装的软件：Oracle Gateway 11g x86_64 linux edition
DATADIRECT_CONNECT64_ODBC_7.0.1for Greenplum （必须是7版本的，6的不行，rhel6带的unixodbc也不行）
( 后记，Oracle 11g database server 中貌似也带了hs的功能，直接使用database server 当gateway应也可，不用再单独安装gateway)
安装路径: Oracle Gateway:/home/oracle/product/11.2.0/tg_1
Datadirect ODBC :/home/oracle/ddodbc
Oracle database ：/home/oracle/app/oracle/product/11.2.0/dbhome_1
配置：
修改/home/oracle/product/11.2.0/tg_1/hs/admin/initdg4odbc.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = Greenplum
#HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_LEVEL= ODBC
HS_FDS_TRACE_FILE_NAME = /home/oracle/odbc_trace.log
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS = FALSE
#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_LANGUAGE = AMERICAN_AMERICA.UTF8
HS_FDS_TIMESTAMP_MAPPING = “TIMESTAMP(6)”
HS_FDS_FETCH_ROWS=1
#HS_FDS_SQLLEN_INTERPRETATION=32
HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
HS_NLS_NCHAR = UCS2
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
set ODBCINST=/etc/odbcinst.ini
修改/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
备注，此处直接使用database的listener。未使用gateway的listener ，在文件后追加：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4odbc)
(ORACLE_HOME =/home/oracle/product/11.2.0/tg_1 )
(ENV=”LD_LIBRARY_PATH=/usr/local/lib:/home/oracle/product/11.2.0/tg_1/lib:/home/oracle/ddodbc/lib”)
(PROGRAM = dg4odbc)
)
)
)
修改/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
增加：
dg4odbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc))
(HS=OK)
)
修改/etc/odbc.ini
[oracle@server3 admin]$ cat /etc/odbc.ini
[ODBC Data Sources]
Greenplum=Greenplum
[ODBC Data Sources]
Greenplum Wire Protocol=DataDirect 7.0 Greenplum Wire Protocol
[ODBC]
IANAAppCodePage=4
InstallDir=/home/oracle/ddodbc
Trace=0
TraceFile=odbctrace.out
TraceDll=/home/oracle/ddodbc/lib/ddtrc26.so
[Greenplum]
Driver=/home/oracle/ddodbc/lib/ddgplm26.so
Description=DataDirect 7.0 Greenplum Wire Protocol
AlternateServers=
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=haier
DefaultLongDataBuffLen=2048
EnableDescribeParam=0
EnableKeysetCursors=0
EncryptionMethod=0
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
HostName=server3
InitializationString=
KeyPassword=
KeysetCursorOptions=0
KeyStore=
KeyStorePassword=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
Password=
Pooling=0
PortNumber=54321
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
XMLDescribeType=-10
Charset=utf8
[ODBC]
TraceFile=/tmp/sql.log
Trace=1
修改/etc/odbcinst.ini
[ODBC Drivers]
DataDirect 7.0 Greenplum Wire Protocol=Installed
[ODBC Translators]
OEM to ANSI=Installed
[Administrator]
HelpRootDirectory=/home/oracle/ddodbc/adminhelp
[ODBC]
#This section must contain values for DSN-less connections
#if no odbc.ini file exists. If an odbc.ini file exists,
#the values from that [ODBC] section are used.
[DataDirect 7.0 Greenplum Wire Protocol]
Driver=/home/oracle/ddodbc/lib/ddgplm26.so
Setup=/home/oracle/ddodbc/lib/ddgplm26.so
APILevel=0
ConnectFunctions=YYY
DriverODBCVer=3.52
FileUsage=0
HelpRootDirectory=/home/oracle/ddodbc/help
SQLLevel=0
重启listener
lsnrctl status 中应有一下内容：
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server3)(PORT=1521)))
Services Summary…
Service “dg4odbc” has 1 instance(s).
Instance “dg4odbc”, status UNKNOWN, has 1 handler(s) for this service…
而后在数据库中创建dblink,注意用户名和密码需要使用双引号
create database link DBLINK
connect to “gpadmin” identified by “gpadmin”
using ‘dg4odbc’;
执行sql语句即可返回成功（注意HAWQ中的表名和字段名需要使用双引号，用小写表示）：
select count(*) from “poc1″@dblink;