Error about reflection

1

Hi,dremio fans:
when I execute the sql:
create vds gpspace.gp_http as select id,"method"as httpmethod,url,host,referer,cookie,“user-agent” as useragent,proxy,reqtype,reqlength,reqheader,reqdata,statuscode,setcookie,resptype,resplength,respheader,respdata,cast(“time” as varchar) happentime from es210.“csp_flow-2018-09-21”.“csp_http”

it build the vds sucess.
but when I reflection the vds to the greenplum datasource table，there happen a error .

ALTER DATASET gpSpace.gp_http
CREATE EXTERNAL REFLECTION “external_gp_http”
USING gp.public.csp_http

greenplum table structure

CREATE TABLE CSP_HTTP
(
ID VARCHAR(100) NOT NULL,
HttpMethod VARCHAR(30) NOT NULL,
URL VARCHAR(300),
Host VARCHAR(50),
Referer VARCHAR(50),
Cookie VARCHAR(1000),
UserAgent VARCHAR(100),
Proxy VARCHAR(100),
ReqType VARCHAR(100),
ReqLength int,
ReqHeader VARCHAR(200),
ReqData VARCHAR(200),
StatusCode int,
SetCookie VARCHAR(400),
RespType VARCHAR(120),
RespLength int,
RespHeader VARCHAR(200),
RespData VARCHAR(200),
HappenTime VARCHAR(50)

) WITH (appendonly=true, orientation=column) DISTRIBUTED BY (ID) ;

error log like this:

2018-09-27 16:39:53,061 [FABRIC-rpc-event-queue] INFO query.logger - {“queryId”:“245368a7-5fc4-4347-e655-a3a85e89f400”,“schema”:“gp.public”,“queryText”:“select ID,“Method"as HttpMethod,URL,Host,Referer,Cookie,“User-Agent” as UserAgent,Proxy,ReqType,ReqLength,ReqHeader,ReqData,StatusCode,SetCookie,RespType,RespLength,RespHeader,RespData,cast(“Time” as varchar) happenTime from es210.“csp_flow-2018-09-21”.“csp_http””,“start”:1538037592692,“finish”:1538037593060,“outcome”:“COMPLETED”,“username”:“root”,“commandDescription”:“execute; query”}
2018-09-27 16:41:10,701 [24536859-6d7e-9907-5f56-308dd8d91d00:foreman] INFO query.logger - {“queryId”:“24536859-6d7e-9907-5f56-308dd8d91d00”,“schema”:”",“queryText”:"[Prepare Statement] SELECT “ID”, “Method” AS “HttpMethod”, “URL”, “Host”, “Referer”, “Cookie”, “User-Agent” AS “UserAgent”, “Proxy”, “ReqType”, “ReqLength”, “ReqHeader”, “ReqData”, “StatusCode”, “SetCookie”, “RespType”, “RespLength”, “RespHeader”, “RespData”, CAST(“Time” AS VARCHAR) AS “happenTime”\nFROM “es210”.“csp_flow-2018-09-21”.“csp_http”",“start”:1538037670519,“finish”:1538037670701,“outcome”:“COMPLETED”,“username”:“root”,“commandDescription”:“prepare; query”}
2018-09-27 16:41:10,754 [24536858-a6ab-473c-71cd-0e136b898600:foreman] INFO query.logger - {“queryId”:“24536858-a6ab-473c-71cd-0e136b898600”,“schema”:“gp.public”,“queryText”:"create VDS gpSpace.gp_http AS select ID,“Method"as HttpMethod,URL,Host,Referer,Cookie,“User-Agent” as UserAgent,Proxy,ReqType,ReqLength,ReqHeader,ReqData,StatusCode,SetCookie,RespType,RespLength,RespHeader,RespData,cast(“Time” as varchar) happenTime from es210.“csp_flow-2018-09-21”.“csp_http””,“start”:1538037670461,“finish”:1538037670754,“outcome”:“COMPLETED”,“username”:“root”,“commandDescription”:“execute and store; direct”}
2018-09-27 16:43:26,167 [245367d1-c9a7-2c7f-5378-46c0597d5000:foreman] INFO c.d.e.p.s.ExternalMaterializationDescriptor - User Error Occurred [ErrorId: 24994304-0e6f-44c7-8efa-c6dbe19ba9e2]
com.dremio.common.exceptions.UserException: External reflection schema does not match Dataset schema
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:746) ~[dremio-common-2.1.6-201809161906440178-edb5b4d.jar:2.1.6-201809161906440178-edb5b4d]
at com.dremio.exec.planner.sql.ExternalMaterializationDescriptor.getMaterializationFor(ExternalMaterializationDescriptor.java:58) [dremio-sabot-kernel-2.1.6-201809161906440178-edb5b4d.jar:2.1.6-201809161906440178-edb5b4d]
at com.dremio.service.reflection.ReflectionServiceImpl.createExternalReflection(ReflectionServiceImpl.java:438) [dremio-services-accelerator-2.1.6-201809161906440178-edb5b4d.jar:2.1.6-201809161906440178-edb5b4d]
at com.dremio.service.reflection.AccelerationManagerImpl.addExternalReflection(AccelerationManagerImpl.java:177) [dremio-services-accelerator-2.1.6-201809161906440178-edb5b4d.jar:2.1.6-201809161906440178-edb5b4d]
at com.dremio.exec.planner.sql.handlers.direct.AccelAddExternalReflectionHandler.toResult(AccelAddExternalReflectionHandler.java:47) [dremio-sabot-kernel-2.1.6-201809161906440178-edb5b4d.jar:2.1.6-201809161906440178-edb5b4d]
at com.dremio.exec.planner.sql.handlers.commands.DirectWriterCommand.plan(DirectWriterCommand.java:87) [dremio-sabot-kernel-2.1.6-201809161906440178-edb5b4d.jar:2.1.6-201809161906440178-edb5b4d]
at com.dremio.exec.work.foreman.AttemptManager.run(AttemptManager.java:302) [dremio-sabot-kernel-2.1.6-201809161906440178-edb5b4d.jar:2.1.6-201809161906440178-edb5b4d]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_152]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_152]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_152]

2

Are you able to query Greenplum as a PDS?

3

Hi,kelly,
thanks your reply. I want to confirmed that ‘PDS’ how to create?

4

PDS = physical dataset.

Can you connect to Greenplum and query? Try using the Postgres connection.

5

Hi,kelly:
thanks!
I can connect greenplum with dremio ，and can query data in greenplum tables.

6

Ok, great!

Do you really want an external reflection, or do you want a normal reflection?

7

nice quickly.
first,I don’t know the distinguish between external and normal.can you give me some url to query?
the second. I want to test the reflection that they are not the same soure(i.e. elasticsearch,greenplum) ,how can I do? thanks

I had just test the VDS then modify the VDS like this:

create VDS gpSpace.a_brand_vds AS select t.* from gp.public.a_brand t
ALTER DATASET gpspace.a_brand_vds
CREATE EXTERNAL REFLECTION “external_gp_http2”
USING gp.public.a_brand

this is just complete same source.
if I want

8

If i use the command:

— ok
create VDS gpSpace.es_vds_http AS select ID,“Method” HttpMethod, URL,Host,Referer,Cookie,“User-Agent” as UserAgent,Proxy,ReqType,ReqLength,ReqHeader,ReqData,StatusCode,SetCookie,RespType,RespLength,RespHeader,RespData,cast(“Time” as varchar) happenTime,SrcIP,SrcPort,DstIP,DstPort,Protocol from es210.“csp_flow-2018-09-21”.“csp_http” LIMIT 50
— GP table

CREATE TABLE CSP_HTTP_VDS
(
id varchar(100) not null,
httpmethod varchar(30) not null,
url varchar(9000),
host varchar(250),
referer varchar(2500),
cookie varchar(6000),
useragent varchar(500),
proxy varchar(100),
reqtype varchar(100),
reqlength int4,
reqheader varchar(200),
reqdata varchar(200),
statuscode int4,
setcookie varchar(4400),
resptype varchar(120),
resplength int4,
respheader varchar(200),
respdata varchar(200),
happentime varchar(50),
srcip varchar(50),
srcport int4,
dstip varchar(50),
dstport int4,
protocol varchar(50)

) WITH (appendonly=true, orientation=column) DISTRIBUTED BY (ID) ;
—GP table end

ALTER DATASET gpSpace.es_vds_http
CREATE EXTERNAL REFLECTION external_gp_http
USING gp135.public.CSP_HTTP_VDS

then is
External reflection schema does not match Dataset schema

9

Sorry, I am having a hard time understanding what you are trying to do. Can you explain?

I think you are creating a physical dataset, then trying to add an external reflection using the same source. Have you tried using the GUI to create a reflection? I think you maybe want a normal aggregation or raw reflection that Dremio manages and not an external reflection.

External reflections are a powerful feature but very advanced.

10

hi,kelly:
so sorry about my expression about the problem.
I want to create normal reflection that the source and the target isn’t in same data source.
can you give me some example?
thanks!

11

Try this: https://www.dremio.com/tutorials/getting-started-with-data-reflections/

12

thanks kelly. I had added reflection.