Some Upserts into standalone collection from s3 external collection are not happening.

Steps:

1. Create s3 link
2. Create an external collection on a given s3 bucket. Able to query the data directly from s3. external_collection_data.json

   CREATE EXTERNAL DATASET `Default`.`Default`.`s3Collection1` ON `copyfroms3` AT `Default`.`Default`.`s3Link` PATH "hotel" WITH {"format":"json"};

3. Create a standalone collection

   CREATE COLLECTION `Default`.`Default`.`collection` PRIMARY KEY (email:string);

4. Run an UPSERT into standalone_collection

   UPSERT INTO `collection` SELECT d.email FROM s3Collection1 AS d;

5. Check the documents in standalone collection 1stUPSERT.json

   select * from `collection` limit 10;

   [

   {

   "collection": {

   "email": "0NOmU1Lm4yv9krLW97hv04ScLYk4bA6vQGibnpC2kklefsua97I4t0dD27m9Rxsdma9U@k2ImCGlYa.com"

   }

   },

   {

   "collection": {

   "email": "31jETWBRD93C82pqlZPyKvCm8J2z9dvybWji9fO3ClNhd6wyKhJHmUzCTS80P22v1VfhjqRgoh4zE@WhqivXX.com"

   }

   },

   {

   "collection": {

   "email": "33LgNyg3QdArIBI1iPnawGmOZZXWaYergJbxP@u7gyi6Dbj.com"

   }

   },

   {

   "collection": {

   "email": "3vL0gtCtSEAMbV06RXdt1qAFauLYXNaMdmdXVtdr@MxX09inwo.com"

   }

   },

   {

   "collection": {

   "email": "4xx4zijv4DXvYS4AawwQogQjdXN1S@cheKzhB22.com"

   }

   },

   {

   "collection": {

   "email": "5SuM1wnSbzkndf8L14jlNCZ4SechsUPJWsg@EC1u6.com"

   }

   },

   {

   "collection": {

   "email": "5UrgyUa0M51UKDkmOvNL7h9PC6z5l7RfK4uUCcp8sQJ1dn5fIgEDV9TW@l3dsLbZEkp.com"

   }

   },

   {

   "collection": {

   "email": "5mZPT7a57s1LVqWvXkgO1FwUWPRj6Kkv31HvEnPTEianBoS5B3ucVwlD7pArs4KbL5TPxj@1jM8gl89.com"

   }

   },

   {

   "collection": {

   "email": "5yZZVkYEWs2PUiEGlxqZLIPQJG3L0SIOfJOou42X46EIVCCBVcfxoREI2@YIGzS9qg7.com"

   }

   },

   {

   "collection": {

   "email": "6GT3sGJsIIdXEG@zmtTeS.com"

   }

   }

   }

6. Run another UPSERT:

   UPSERT INTO `collection` SELECT

   s.address AS address,

   s.avg_ratings AS avg_ratings,

   s.characters_with_spaces AS characters_with_spaces,

   s.characters_without_spaces AS characters_without_spaces,

   s.city AS city,

   s.country AS country,

   s.document_size AS document_size,

   s.email AS email,

   s.free_breakfast AS free_breakfast,

   s.free_parking AS free_parking,

   s.mutated AS mutated,

   s.name AS name,

   s.padding AS padding,

   s.phone AS phone,

   s.price AS price,

   pl AS public_likes,

   r AS reviews,

   s.`type` AS `type`,

   s.url AS url

   FROM s3Collection1 s

   UNNEST s.public_likes AS pl

   UNNEST s.reviews AS r;

7. Recheck the documents in standalone collection 2ndUPSERT.json

   [

   {

   "collection": {

   "email": "0NOmU1Lm4yv9krLW97hv04ScLYk4bA6vQGibnpC2kklefsua97I4t0dD27m9Rxsdma9U@k2ImCGlYa.com",

   "address": "hoQjUR1OTNFk3QEIckIrBmZp8cke22",

   "avg_ratings": 9.214057037020018,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": "sD06UM",

   "country": "WGPl2oktUh1AvLfVI9P4Lz1enokQHYvA7FgGBbSTcLOeoEOnmM5AczcylarC7vIoZfULkgX7pYb bHczYQxo2XU2NdZk4 ",

   "document_size": 1000,

   "free_breakfast": 0,

   "free_parking": 0,

   "mutated": 0,

   "name": "XtyLUnt",

   "padding": "",

   "phone": 9270,

   "price": 9000,

   "public_likes": "v2 be5efrQ0FXjHR",

   "reviews": {

   "date": "Thu Jan 04 16:06:00 IST 2024",

   "author": "liEQywCqfBBqQoEF d",

   "rating": {

   "cleanliness": 1.0277738550117932,

   "overall": 6.51599030255427,

   "value": 5.189820374130085

   }

   },

   "type": "Hotel",

   "url": "www.0NOmU1Lm4yv9krLW97hv04ScLYk4bA6vQGibnpC2kklefsua97I4t0dD27m9Rxsdma9U.k2ImCGlYa.com"

   }

   },

   {

   "collection": {

   "email": "31jETWBRD93C82pqlZPyKvCm8J2z9dvybWji9fO3ClNhd6wyKhJHmUzCTS80P22v1VfhjqRgoh4zE@WhqivXX.com"

   }

   },

   {

   "collection": {

   "email": "33LgNyg3QdArIBI1iPnawGmOZZXWaYergJbxP@u7gyi6Dbj.com",

   "address": "EJqch4DdhnjsYmYEMELlTEd9xa7n VYmNheLBJp9GvCNxceDnu6IZ7y7Ye3Jb5IQdakZF2wpcrjrUnSlSeVF3Bg5eZdc",

   "avg_ratings": 8.256621943311345,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": "QJzMJTPWwxYDOPyd",

   "country": "RRz6xIlpUBm75sszmy2xbLRSQZe4Hu8LuDG75TVy3LioucvLl75yPxqOMQE85HRJgOEx9X",

   "document_size": 1000,

   "free_breakfast": 1,

   "free_parking": 1,

   "mutated": 0,

   "name": "vX0BF",

   "padding": "",

   "phone": 719,

   "price": 9000,

   "public_likes": "clw3betAAlc 2p7a8WXz",

   "reviews": {

   "date": "Thu Jan 04 15:57:02 IST 2024",

   "author": "OzSXTRlgETAeppuZj",

   "rating": {

   "cleanliness": 8.039299863308361,

   "overall": 4.709602851796067,

   "value": 5.284229193670739

   }

   },

   "type": "Hotel",

   "url": "www.33LgNyg3QdArIBI1iPnawGmOZZXWaYergJbxP.u7gyi6Dbj.com"

   }

   },

   {

   "collection": {

   "email": "3vL0gtCtSEAMbV06RXdt1qAFauLYXNaMdmdXVtdr@MxX09inwo.com",

   "address": "cK8gIDaXFhkFQPV8gQV0WloNEZANBAXZpi5OMUPUawO2ehLJtL2V1X6",

   "avg_ratings": 5.250453036187653,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": "JeF38NeftwM",

   "country": "WzINYwmWNONTIBjpOQjPIjtBjXxPa OVww3yz1Tks2emRuxmM19o6k",

   "document_size": 1000,

   "free_breakfast": 0,

   "free_parking": 0,

   "mutated": 0,

   "name": "SyvI2U md2iy",

   "padding": "",

   "phone": 268,

   "price": 3000,

   "public_likes": "pMHbldY5RiQjRIio",

   "reviews": {

   "date": "Thu Jan 04 15:59:18 IST 2024",

   "author": "BKpCxMpzxtxpqsnDxkye",

   "rating": {

   "cleanliness": 5.326932569144271,

   "overall": 4.153627335953972,

   "value": 7.340157635451813

   }

   },

   "type": "Hotel",

   "url": "www.3vL0gtCtSEAMbV06RXdt1qAFauLYXNaMdmdXVtdr.MxX09inwo.com"

   }

   },

   {

   "collection": {

   "email": "4xx4zijv4DXvYS4AawwQogQjdXN1S@cheKzhB22.com",

   "address": "Okg6JuNpJnxagiDrhIvavdDP2SiyMQ6ZmoXsqbA0NwJyaQQ5GtdSJm0FF5F0zm",

   "avg_ratings": 5.358112342818259,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": "GJ0NRoJ",

   "country": "VSDoLb8Olgqxhj2lTlf3GShVvLMdrU0D1UDLMvFMPo1Q7ooXQOh",

   "document_size": 1000,

   "free_breakfast": 1,

   "free_parking": 1,

   "mutated": 0,

   "name": "wMKGdU6On",

   "padding": "",

   "phone": 4983,

   "price": 5000,

   "public_likes": "I01nnu2 l35A6o1Utbaw",

   "reviews": {

   "date": "Thu Jan 04 16:00:14 IST 2024",

   "author": "LKJvryCzkjVJq",

   "rating": {

   "cleanliness": 8.75664495995218,

   "overall": 7.364526129904007,

   "value": 6.927209078146425

   }

   },

   "type": "Hotel",

   "url": "www.4xx4zijv4DXvYS4AawwQogQjdXN1S.cheKzhB22.com"

   }

   },

   {

   "collection": {

   "email": "5SuM1wnSbzkndf8L14jlNCZ4SechsUPJWsg@EC1u6.com",

   "address": "XldqHsmxV6WhP7DeREUNILSsKQOjkTyHo hsO8MPZ8WpsXSvR2GMt2",

   "avg_ratings": 2.584969472649864,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": "5h2oA3yzlkc4",

   "country": "1c1W35XdXrnKJlJ6y4AmXXib4r7zBlWD3b90aigPSQgr",

   "document_size": 1000,

   "free_breakfast": 1,

   "free_parking": 1,

   "mutated": 0,

   "name": "4pU5 hyyKcc",

   "padding": "",

   "phone": 6822,

   "price": 8000,

   "public_likes": "8IDDorLJWyI476P0E ",

   "reviews": {

   "date": "Thu Jan 04 15:55:57 IST 2024",

   "author": "SrdDuZMenfLKVtTOsQ",

   "rating": {

   "cleanliness": 7.640584406096579,

   "overall": 3.190919926704053,

   "value": 4.9104179425244885

   }

   },

   "type": "Hotel",

   "url": "www.5SuM1wnSbzkndf8L14jlNCZ4SechsUPJWsg.EC1u6.com"

   }

   },

   {

   "collection": {

   "email": "5UrgyUa0M51UKDkmOvNL7h9PC6z5l7RfK4uUCcp8sQJ1dn5fIgEDV9TW@l3dsLbZEkp.com",

   "address": "NZIFBtnY2BUSuitlHba5EcywfGXzWXtVioDS hXzRiUqYpfH",

   "avg_ratings": 9.862090216860697,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": "6HaiHdr",

   "country": "z2r95XwWdDp",

   "document_size": 1000,

   "free_breakfast": 0,

   "free_parking": 0,

   "mutated": 0,

   "name": "kWMq1UlrWFpizldz2",

   "padding": "wDqqHKGofIhVpVPDThliCZVjtziBuxLdariyvAgXMwmVERtBKEyYY",

   "phone": 2637,

   "price": 4000,

   "public_likes": "M3HaI7fiUrGkeZAAV",

   "reviews": {

   "date": "Thu Jan 04 16:05:40 IST 2024",

   "author": "FkwKLBUbZinEqUBO",

   "rating": {

   "cleanliness": 3.7608817690855822,

   "overall": 2.4589359781136446,

   "value": 3.1615775979818195

   }

   },

   "type": "Hotel",

   "url": "www.5UrgyUa0M51UKDkmOvNL7h9PC6z5l7RfK4uUCcp8sQJ1dn5fIgEDV9TW.l3dsLbZEkp.com"

   }

   },

   {

   "collection": {

   "email": "5mZPT7a57s1LVqWvXkgO1FwUWPRj6Kkv31HvEnPTEianBoS5B3ucVwlD7pArs4KbL5TPxj@1jM8gl89.com",

   "address": "9sPCMQS11ykcSc78hXKUxAmV4LkszAHSL0ZCKIny8QXvuXZqHn",

   "avg_ratings": 3.6081516550389567,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": "WWEpIDRh4gHemNytR",

   "country": "l6Gh9J9xKtyZI8NCypWC2N9lZqs qK9m11K9O6uO6TIcrwOTJkOFn9LAOg4S",

   "document_size": 1000,

   "free_breakfast": 0,

   "free_parking": 0,

   "mutated": 0,

   "name": "dO42XNp",

   "padding": "",

   "phone": 8655,

   "price": 8000,

   "public_likes": "xAaCZ1CrTI8snAMLYtP ",

   "reviews": {

   "date": "Thu Jan 04 15:58:07 IST 2024",

   "author": "oTKMheN qGwWyP",

   "rating": {

   "cleanliness": 7.844700841128057,

   "overall": 9.26797248045331,

   "value": 9.369484745275667

   }

   },

   "type": "Hotel",

   "url": "www.5mZPT7a57s1LVqWvXkgO1FwUWPRj6Kkv31HvEnPTEianBoS5B3ucVwlD7pArs4KbL5TPxj.1jM8gl89.com"

   }

   },

   {

   "collection": {

   "email": "5yZZVkYEWs2PUiEGlxqZLIPQJG3L0SIOfJOou42X46EIVCCBVcfxoREI2@YIGzS9qg7.com",

   "address": "7WUm JUG8MFly0zV7yUictg25nsLJBYsrsWeGDAeiDNatsaEVOjhYfd0QIhzudufM9HuLL4BTJF1hrT",

   "avg_ratings": 1.577645611866968,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": "0B26dOGWtcpN9GMW",

   "country": "vvzHF3guTsfRb8w19QQ",

   "document_size": 1000,

   "free_breakfast": 0,

   "free_parking": 0,

   "mutated": 0,

   "name": "5JwQ4J",

   "padding": "",

   "phone": 8411,

   "price": 6000,

   "public_likes": "RyvGFWM6bFtB xj",

   "reviews": {

   "date": "Thu Jan 04 16:02:54 IST 2024",

   "author": "IykATUJWfwz",

   "rating": {

   "cleanliness": 3.5710230259986675,

   "overall": 7.0407911496486175,

   "value": 1.6084943367272586

   }

   },

   "type": "Hotel",

   "url": "www.5yZZVkYEWs2PUiEGlxqZLIPQJG3L0SIOfJOou42X46EIVCCBVcfxoREI2.YIGzS9qg7.com"

   }

   },

   {

   "collection": {

   "email": "6GT3sGJsIIdXEG@zmtTeS.com",

   "address": "9xoItkh2C vKHImrQXhb5UJveiCodPDLftKaeP7 APLVKPEoI",

   "avg_ratings": 3.309040297350939,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": "AtPWJPLEC8m5g4A9",

   "country": "m1MSWgIbYNtlhbWuPZq79RxheaYJ23hxMtF51c0Pspqu jEHOpgWjZU24z6A OuwRFduNav7FVdmiERMUQAZcjM1IKm1Bg",

   "document_size": 1000,

   "free_breakfast": 1,

   "free_parking": 0,

   "mutated": 0,

   "name": "oI8tVD u",

   "padding": "",

   "phone": 1332,

   "price": 1000,

   "public_likes": "VTTJQiQNknKI5dk",

   "reviews": {

   "date": "Thu Jan 04 16:06:31 IST 2024",

   "author": "iuIEwbpPqMM",

   "rating": {

   "cleanliness": 8.087269453436846,

   "overall": 3.298774698120174,

   "value": 9.102874168264046

   }

   },

   "type": "Hotel",

   "url": "www.6GT3sGJsIIdXEG.zmtTeS.com"

   }

   },

   {

   "collection": {

   "email": "7G4hzPKR1bttOEhWD84vNvDLG@lAo5yuu96a.com"

   }

   },

   {

   "collection": {

   "email": "94jgUfEpa729F2jVGUHcJDITZ9SsAqGiY6KqM6wYFzvlwX3ZcPIdtLYXLm96x1pd@DaeOS.com",

   "address": "icCQGGXTaILZPphnBytdVUcAO5akwxrL4Z2IkbTFFR",

   "avg_ratings": 8.790328930898427,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": " T0dMUJ",

   "country": "uxP77pYr63QMXiPcZNDCN",

   "document_size": 1000,

   "free_breakfast": 1,

   "free_parking": 1,

   "mutated": 0,

   "name": "hvYo8jyGLUxbvHtCXsRN",

   "padding": "n",

   "phone": 2463,

   "price": 1000,

   "public_likes": "XIFPmH0jebED2OyytOH",

   "reviews": {

   "date": "Thu Jan 04 16:05:42 IST 2024",

   "author": "wVHVXuOmnkoeBK",

   "rating": {

   "cleanliness": 6.509339973646856,

   "overall": 7.274942608581519,

   "value": 4.075455026060228

   }

   },

   "type": "Hotel",

   "url": "www.94jgUfEpa729F2jVGUHcJDITZ9SsAqGiY6KqM6wYFzvlwX3ZcPIdtLYXLm96x1pd.DaeOS.com"

   }

   },

   {

   "collection": {

   "email": "9FS275zJjhORqq8WnBe3cyOmEVSXtSSSZazab55ZJ63kIUdGk2OyRuGxbPu6ao86pTLMYZI09H0vBBUQohQBBYFf49t1eUM6j3K@QYS4ZjJq.com",

   "address": "zLpHUL5PYzSaHVuewoE 0rKpkyCaodv2WTgvJE4mOf12JbSURyUSnIW",

   "avg_ratings": 4.365628560265891,

   "characters_with_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ",

   "characters_without_spaces": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",

   "city": "bipxSyaBS8NApGxwW0",

   "country": "7L1u1SJWkrWEoalW0wbe9L44bPRcr8gWDh2sBHjwhAt3s4sPq5z9cPSLoR6GxQFM9nqXi",

   "document_size": 1000,

   "free_breakfast": 1,

   "free_parking": 1,

   "mutated": 0,

   "name": "5ehJuQ",

   "padding": "",

   "phone": 3711,

   "price": 5000,

   "public_likes": "mkpAU6dCUwWQ",

   "reviews": {

   "date": "Thu Jan 04 16:02:54 IST 2024",

   "author": "VkMgPNGk bmY",

   "rating": {

   "cleanliness": 8.180588335193109,

   "overall": 7.117464729635596,

   "value": 0.5012956341709895

   }

   },

   "type": "Hotel",

   "url": "www.9FS275zJjhORqq8WnBe3cyOmEVSXtSSSZazab55ZJ63kIUdGk2OyRuGxbPu6ao86pTLMYZI09H0vBBUQohQBBYFf49t1eUM6j3K.QYS4ZjJq.com"

   }

   },

   {

   "collection": {

   "email": "9wGpnXSeeopLXSSH@YW7XzBpWa.com"

   }

   },

   {

   "collection": {

   "email": "DeaCpMS75xzj2brf2e@Bez2shmu9.com"

   }

   }

   ]

8. As we can see not everything is upserted. Total documents in s3 bucket is 7675
9. Total documents on querying external collection is 7675
10. Total documents on querying standalone collection is 7675
11. While documents properly upserted is 6336
12. And this can be verified as:

    select count(*) from `collection` s where s.address is missing;

    [

    {

    "$1": 1339

    }

    ]