{"action":"created","issue":{"url":"https://api.github.com/repos/awslabs/aws-lambda-redshift-loader/issues/32","labels_url":"https://api.github.com/repos/awslabs/aws-lambda-redshift-loader/issues/32/labels{/name}","comments_url":"https://api.github.com/repos/awslabs/aws-lambda-redshift-loader/issues/32/comments","events_url":"https://api.github.com/repos/awslabs/aws-lambda-redshift-loader/issues/32/events","html_url":"https://github.com/awslabs/aws-lambda-redshift-loader/issues/32","id":95286560,"number":32,"title":"Add Support for Specifying Column Names to support Defaulted Columns","user":{"login":"FrancisCSLau","id":1772708,"avatar_url":"https://avatars.githubusercontent.com/u/1772708?v=3","gravatar_id":"","url":"https://api.github.com/users/FrancisCSLau","html_url":"https://github.com/FrancisCSLau","followers_url":"https://api.github.com/users/FrancisCSLau/followers","following_url":"https://api.github.com/users/FrancisCSLau/following{/other_user}","gists_url":"https://api.github.com/users/FrancisCSLau/gists{/gist_id}","starred_url":"https://api.github.com/users/FrancisCSLau/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/FrancisCSLau/subscriptions","organizations_url":"https://api.github.com/users/FrancisCSLau/orgs","repos_url":"https://api.github.com/users/FrancisCSLau/repos","events_url":"https://api.github.com/users/FrancisCSLau/events{/privacy}","received_events_url":"https://api.github.com/users/FrancisCSLau/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"IanMeyers","id":6339696,"avatar_url":"https://avatars.githubusercontent.com/u/6339696?v=3","gravatar_id":"","url":"https://api.github.com/users/IanMeyers","html_url":"https://github.com/IanMeyers","followers_url":"https://api.github.com/users/IanMeyers/followers","following_url":"https://api.github.com/users/IanMeyers/following{/other_user}","gists_url":"https://api.github.com/users/IanMeyers/gists{/gist_id}","starred_url":"https://api.github.com/users/IanMeyers/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/IanMeyers/subscriptions","organizations_url":"https://api.github.com/users/IanMeyers/orgs","repos_url":"https://api.github.com/users/IanMeyers/repos","events_url":"https://api.github.com/users/IanMeyers/events{/privacy}","received_events_url":"https://api.github.com/users/IanMeyers/received_events","type":"User","site_admin":false},"milestone":null,"comments":5,"created_at":"2015-07-15T20:45:49Z","updated_at":"2015-08-13T08:12:13Z","closed_at":null,"body":"We want a column in a table in Redshift that defaults to GetDate() so that we know when the row is copied into Redshift. But we cannot get it work work with code changes to the Loader\r\n\r\nHere is the walk-thru. \r\n\r\n**Table Definition:**\r\nField is shown below \"dbImportDateTime\"\r\n\r\nCREATE TABLE userData (\r\n  userID bigint,\r\n  userInsertDateTime timestamp ,\r\n  firstName varchar(200) ,\r\n  domain varchar(400) ,\r\n  languageFriendly varchar(200) ,\r\n  countryFriendly varchar(200) ,\r\n  SharedUser smallint,\r\n  SignupBucket varchar(200) ,\r\n  SignupSource varchar(400) ,\r\n  SignupSubSource varchar(400) ,\r\n  SignupCampaign varchar(200) ,\r\n  SignupSegment varchar(200) ,\r\n  selectedRole varchar(400),\r\n  dbImportDateTime timestamp DEFAULT GETDATE(),\r\n  PRIMARY KEY (userID) \r\n  )\r\n  DISTKEY(userID)\r\n  COMPOUND SORTKEY(userID);  \r\n\r\n\r\n**Copy Command Generated by Redshift Loader Lambda function**\r\n\r\nCOPY userData \r\n\t\tfrom 's3://redshift-copy-manifests/user-data/manifest-2015-07-15-20:06:10-7234' \r\n\t\twith credentials as SNIP\r\n    manifest \r\n    json 's3://json-paths/user-data.txt';\r\n\r\n**Error from Copy Command**\r\n\r\nAn error occurred when executing the SQL command:\r\n[Amazon](500310) Invalid operation: Number of jsonpaths and the number of columns should match. JSONPath size: 13, Number of columns in table or column list: 14;\r\n\r\n\r\n**JSON Path File**\r\n{ \r\n    \"jsonpaths\": [\r\n         \"$.userID\",\r\n         \"$.userInsertDateTime\",\r\n         \"$.firstName\",\r\n         \"$.domain\",\r\n         \"$.languageFriendly\",\r\n         \"$.countryFriendly\",\r\n         \"$.SharedUser\",\r\n         \"$.SignupBucket\",\r\n         \"$.SignupSource\",\r\n         \"$.SignupSubSource\",\r\n         \"$.SignupCampaign\",\r\n         \"$.SignupSegment\",\r\n         \"$.selectedRole\"\r\n    ]\r\n}\r\n\r\n**Summary of Error**\r\nThe error message is accurate. JSON Path have 13 fields and Table has 14 fields but I was hoping that the 14th field will be filled in with the Default of GetDate()\r\n\r\n**Solution**\r\nWe found that by adding the column names, the copy works and the last column is defaulted to the load date.  Copy command shown below.\r\n\r\nCOPY userData (\r\n\tuserID,\r\n\tuserInsertDateTime,\r\n\tfirstName,\r\n\tdomain,\r\n\tlanguageFriendly,\r\n\tcountryFriendly,\r\n\tSharedUser,\r\n\tSignupBucket,\r\n\tSignupSource,\r\n\tSignupSubSource,\r\n\tSignupCampaign,\r\n\tSignupSegment,\r\n\tselectedRole\r\n\t\t)\r\n\t\tfrom 's3://redshift-copy-manifests/user-data/manifest-2015-07-15-20:06:10-7234' \r\n\t\twith credentials as SNIP\r\n    manifest \r\n    json 's3://json-paths/user-data.txt';\r\n\r\n\r\n**Code Changes Needed**\r\nWe will need to make some code changes to the Lambda Loader but I thought I check with you before making such a change to make sure we are going down the right path and that you will merge our code change back into your codebase. \r\n\r\n1. Add support for a new column in the LambdaRedshiftBatchLoadConfig table to support specifying comma delimited column names. If null, copy command will not include the column names. If specified, column names are added to copy command\r\n\r\n2. Update setup.js to support this new column\r\n\r\n3. Update index.js to include column names in copy command if provided in config table\r\n\r\nIs this the right approach? Any other things we should watch out for?"},"comment":{"url":"https://api.github.com/repos/awslabs/aws-lambda-redshift-loader/issues/comments/130570921","html_url":"https://github.com/awslabs/aws-lambda-redshift-loader/issues/32#issuecomment-130570921","issue_url":"https://api.github.com/repos/awslabs/aws-lambda-redshift-loader/issues/32","id":130570921,"user":{"login":"IanMeyers","id":6339696,"avatar_url":"https://avatars.githubusercontent.com/u/6339696?v=3","gravatar_id":"","url":"https://api.github.com/users/IanMeyers","html_url":"https://github.com/IanMeyers","followers_url":"https://api.github.com/users/IanMeyers/followers","following_url":"https://api.github.com/users/IanMeyers/following{/other_user}","gists_url":"https://api.github.com/users/IanMeyers/gists{/gist_id}","starred_url":"https://api.github.com/users/IanMeyers/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/IanMeyers/subscriptions","organizations_url":"https://api.github.com/users/IanMeyers/orgs","repos_url":"https://api.github.com/users/IanMeyers/repos","events_url":"https://api.github.com/users/IanMeyers/events{/privacy}","received_events_url":"https://api.github.com/users/IanMeyers/received_events","type":"User","site_admin":false},"created_at":"2015-08-13T08:12:13Z","updated_at":"2015-08-13T08:12:13Z","body":"Hello,\r\n\r\nYes, the changes are now available in the setup and index.js. I'm working on an enhancement to log when ProvisionedThroughputExceeded exceptions happen on tables, and then will ship a fully built 2.0.6 zip. Furthermore, I'm changing the packaging model, so that only upgrades, index and kmsCrypto are built into the Lambda deployment zip. This will minimise the size of the function, but will require that customers use the base javascript files for setup and maintenance activities.\r\n\r\nRegards,\r\n\r\nIan\r\n\r\np.s.\r\n\r\nUsing the file header to resolve columns would be quite neat, but given the processing model today I don't think it will be possible."}}