How to create external tables in dedicated serverless sql pool for json file stored in gen2

Question

[
  {
    "id": 1,
    "title": "To Kill a Mockingbird",
    "author": "Harper Lee",
    "genre": "Fiction",
    "year": 1960
  },
  {
    "id": 2,
    "title": "1984",
    "author": "George Orwell",
    "genre": "Dystopian",
    "year": 1949
  },
  {
    "id": 3,
    "title": "Pride and Prejudice",
    "author": "Jane Austen",
    "genre": "Classic",
    "year": 1813
  }
]
this is my data i have created a data source but for creating external table showing errors

Answer 1

@Suruli Rajan Welcome to Microsoft Q&A forum and thanks for reaching out here.

Azure Synapse SQL External tables do not support JSON format, for a list of support formats for external tables please refer to this document - Use external tables with Synapse SQL

As a workaround, you can use Openrowset and Cross Apply OPENJSON (JSON views) to achieve the same. For more info please refer to this doc - JSON views.

As there is no out of box supported feature, I encourage you to please log feedback in Azure Synapse user voice forum here - https://feedback.azure.com/d365community/forum/9b9ba8e4-0825-ec11-b6e6-000d3a4f07b8

All the feedback shared in this forum are actively monitored and reviewed by respective product owners.

Hope this helps

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.