Data Lake Analytics - How to select/insert into a table with fewer columns?

Question

I have a table that I want to output the results of a query to.

The destination table has less columns than the source table.

Running a simple insert select statement results in the below error:

E_CSC_USER_INSERTTOOMANYCOLUMNSSPECIFIESPARTITION: The source for a single partition INSERT statement contains more items than the target's actual columns. Description: The number of source columns may not exceed the number of actual target table columns. Virtual columns should not be provided in the source rowset. Resolution: Match the schema of the source to the actual (non-virtual) columns

I've tried using a view that limits the columns down to the correct set as an intermediary, but this still gives the same error.

How can I move data from one table to another in data lake analytics, when I must always have the same columns in both tables?

EDIT:

Sample DDL

Create table:

CREATE TABLE dbo.log
(
    DateStamp DateTime,
    code string,
    ipAddresses string,
    method string,
    column4 string,
    column5 string,
    column6 string,
    url string,
    userAgent string,
    queryString string,
    cookie string,
    column11 string,
    column12 string,
    column13 string,
    column14 string,
    column15 string,
    column16 string,
    Query_a1 string,
    Query_c1 string,
    Query_c2 string,
    Query_a2 string,
    Query_z string,
    Query_l string,
    [Cookie_ID] string,

    INDEX clx_log
    CLUSTERED(Query_a,Query_l ASC)

)  
PARTITIONED BY (DateStamp)
DISTRIBUTED BY ROUND ROBIN;

Table to insert into

CREATE TABLE dbo.[item_views]
(
    DateStamp DateTime,
    a string,
    c1 string,
    c2 string,
    l  string,
    Cookie_ID string,
    source string,
    INDEX clx_item_views
    CLUSTERED(c1, l ASC)
)
PARTITIONED BY (DateStamp)
DISTRIBUTED BY HASH (l);

Insert statement that causes the exception:

INSERT dbo.[item_views]
    (
      DateStamp,
    a ,
    c1,
    c2,
    l,
    Cookie_ID,
    source 
    )

PARTITION (@partition1)
SELECT 
           DateStamp,
            Query_a1,
            Query_c1,
            Query_c2,
            Query_l,
            [Cookie_Id],
            "abcd"
FROM dbo.log
 WHERE DateStamp.Date == "2017-01-01";

Can you provide a small repro? Are you specifying all column names in both the insert and select eg INSERT ( col1, col2 ... ) SELECT col1, col2 etc — wBob
I'm specifying the exact same columns in both the insert ( col1...) and select statements. It get's upset if there are any other columns that exist in the source table, but aren't included (as per error) — Neil P
You will need to post your code or an obfuscated version of it, and the DDL for the target table and preferably some sample data. I cannot reproduce this error at this point. — wBob
@wbob done. I've got lots of USQL that works in a similar way, but it always uses all of the source columns, so I've never hit this before — Neil P

wBob wBob · Accepted Answer · 2018-01-10T14:45:00

Your example is slightly confusing because it doesn't compile but what the error is basically saying is: if you are inserting into a partitioned table, you do not have to include the partitioning column in the column and insert list because you specify it using the PARTITION clause instead, eg something like:

INSERT dbo.[item_views]
(
    a,
    c1,
    c2,
    l,
    Cookie_ID,
    source
)
PARTITION
(
    @partition1
)
SELECT Query_a1,
       Query_c1,
       Query_c2,
       Query_l,
       Cookie_ID,
       "abcd" AS source
FROM dbo.log
WHERE DateStamp.Date == "2017-01-01";

Data Lake Analytics - How to select/insert into a table with fewer columns?

1 Answers