Table metadata should display using data in socrata_imports table

simonw commented 2 years ago

Imports currently look like this:

There's no indication of what the data is or where it came from!

simonw commented 2 years ago

In this case there's a ton of data in that table:

Full metadata includes both license AND columns:

{
    "id": "8pt5-q6wp",
    "name": "Indicators of Anxiety or Depression Based on Reported Frequency of Symptoms During Last 7 Days",
    "assetType": "dataset",
    "attribution": "NCHS/DHIS",
    "averageRating": 0,
    "category": "NCHS",
    "createdAt": 1589990736,
    "description": "The U.S. Census Bureau, in collaboration with five federal agencies, launched the Household Pulse Survey to produce data on the social and economic impacts of Covid-19 on American households.  The Household Pulse Survey was designed to gauge the impact of the pandemic on employment status, consumer spending, food security, housing, education disruptions, and dimensions of physical and mental wellness.\n\nThe survey was designed to meet the goal of accurate and timely weekly estimates. It was conducted by an internet questionnaire, with invitations to participate sent by email and text message. The sample frame is the Census Bureau Master Address File Data. Housing units linked to one or more email addresses or cell phone numbers were randomly selected to participate, and one respondent from each housing unit was selected to respond for him or herself. Estimates are weighted to adjust for nonresponse and to match Census Bureau estimates of the population by age, gender, race and ethnicity, and educational attainment. All estimates shown meet the NCHS Data Presentation Standards for Proportions,",
    "displayType": "table",
    "downloadCount": 34731,
    "hideFromCatalog": false,
    "hideFromDataJson": false,
    "licenseId": "USGOV_WORKS",
    "newBackend": true,
    "numberOfComments": 0,
    "oid": 38411991,
    "provenance": "official",
    "publicationAppendEnabled": false,
    "publicationDate": 1650465349,
    "publicationGroup": 17141804,
    "publicationStage": "published",
    "rowsUpdatedAt": 1652883071,
    "rowsUpdatedBy": "ki96-txhe",
    "tableId": 18676685,
    "totalTimesRated": 0,
    "viewCount": 31610,
    "viewLastModified": 1652883502,
    "viewType": "tabular",
    "approvals": [
        {
            "reviewedAt": 1590009957,
            "reviewedAutomatically": true,
            "state": "approved",
            "submissionId": 4635361,
            "submissionObject": "public_audience_request",
            "submissionOutcome": "change_audience",
            "submittedAt": 1590009957,
            "workflowId": 2100,
            "submissionDetails": {
                "permissionType": "READ"
            },
            "submissionOutcomeApplication": {
                "endedAt": 1590009958,
                "failureCount": 0,
                "startedAt": 1590009957,
                "status": "success"
            },
            "submitter": {
                "id": "ki96-txhe",
                "displayName": "NCHS"
            }
        }
    ],
    "clientContext": {
        "clientContextVariables": [],
        "inheritedVariables": {}
    },
    "columns": [
        {
            "id": 559238944,
            "name": "Indicator",
            "dataTypeName": "text",
            "description": "",
            "fieldName": "indicator",
            "position": 1,
            "renderTypeName": "text",
            "tableColumnId": 102255558,
            "cachedContents": {
                "non_null": "10152",
                "largest": "Symptoms of Depressive Disorder",
                "null": "0",
                "top": [
                    {
                        "item": "Symptoms of Anxiety Disorder or Depressive Disorder",
                        "count": "3384"
                    },
                    {
                        "item": "Symptoms of Anxiety Disorder",
                        "count": "3384"
                    },
                    {
                        "item": "Symptoms of Depressive Disorder",
                        "count": "3384"
                    }
                ],
                "smallest": "Symptoms of Anxiety Disorder",
                "count": "10152",
                "cardinality": "3"
            },
            "format": {}
        },
        {
            "id": 559238945,
            "name": "Group",
            "dataTypeName": "text",
            "description": "",
            "fieldName": "group",
            "position": 2,
            "renderTypeName": "text",
            "tableColumnId": 102255559,
            "cachedContents": {
                "non_null": "10152",
                "largest": "National Estimate",
                "null": "0",
                "top": [
                    {
                        "item": "By State",
                        "count": "6885"
                    },
                    {
                        "item": "By Age",
                        "count": "1071"
                    },
                    {
                        "item": "By Race/Hispanic ethnicity",
                        "count": "765"
                    },
                    {
                        "item": "By Education",
                        "count": "612"
                    },
                    {
                        "item": "By Sex",
                        "count": "306"
                    },
                    {
                        "item": "National Estimate",
                        "count": "153"
                    },
                    {
                        "item": "By Disability status",
                        "count": "126"
                    },
                    {
                        "item": "By Gender identity",
                        "count": "117"
                    },
                    {
                        "item": "By Sexual orientation",
                        "count": "117"
                    }
                ],
                "smallest": "By Age",
                "count": "10152",
                "cardinality": "9"
            },
            "format": {}
        },
        {
            "id": 559238946,
            "name": "State",
            "dataTypeName": "text",
            "description": "",
            "fieldName": "state",
            "position": 3,
            "renderTypeName": "text",
            "tableColumnId": 102255560,
            "cachedContents": {
                "non_null": "10152",
                "largest": "Wyoming",
                "null": "0",
                "top": [
                    {
                        "item": "United States",
                        "count": "3267"
                    },
                    {
                        "item": "Texas",
                        "count": "135"
                    },
                    {
                        "item": "Oregon",
                        "count": "135"
                    },
                    {
                        "item": "Idaho",
                        "count": "135"
                    },
                    {
                        "item": "Alaska",
                        "count": "135"
                    },
                    {
                        "item": "Alabama",
                        "count": "135"
                    },
                    {
                        "item": "Oklahoma",
                        "count": "135"
                    },
                    {
                        "item": "North Carolina",
                        "count": "135"
                    },
                    {
                        "item": "Colorado",
                        "count": "135"
                    },
                    {
                        "item": "Mississippi",
                        "count": "135"
                    },
                    {
                        "item": "Florida",
                        "count": "135"
                    },
                    {
                        "item": "Vermont",
                        "count": "135"
                    },
                    {
                        "item": "Delaware",
                        "count": "135"
                    },
                    {
                        "item": "Nevada",
                        "count": "135"
                    },
                    {
                        "item": "Louisiana",
                        "count": "135"
                    },
                    {
                        "item": "New York",
                        "count": "135"
                    },
                    {
                        "item": "West Virginia",
                        "count": "135"
                    },
                    {
                        "item": "South Carolina",
                        "count": "135"
                    },
                    {
                        "item": "New Jersey",
                        "count": "135"
                    },
                    {
                        "item": "New Mexico",
                        "count": "135"
                    }
                ],
                "smallest": "Alabama",
                "count": "10152",
                "cardinality": "52"
            },
            "format": {}
        },
        {
            "id": 559238947,
            "name": "Subgroup",
            "dataTypeName": "text",
            "description": "",
            "fieldName": "subgroup",
            "position": 4,
            "renderTypeName": "text",
            "tableColumnId": 102255561,
            "cachedContents": {
                "non_null": "10152",
                "largest": "Wyoming",
                "null": "0",
                "top": [
                    {
                        "item": "Bachelor's degree or higher",
                        "count": "153"
                    },
                    {
                        "item": "Female",
                        "count": "153"
                    },
                    {
                        "item": "80 years and above",
                        "count": "153"
                    },
                    {
                        "item": "Non-Hispanic Asian, single race",
                        "count": "153"
                    },
                    {
                        "item": "Less than a high school diploma",
                        "count": "153"
                    },
                    {
                        "item": "Some college/Associate's degree",
                        "count": "153"
                    },
                    {
                        "item": "High school diploma or GED",
                        "count": "153"
                    },
                    {
                        "item": "Male",
                        "count": "153"
                    },
                    {
                        "item": "60 - 69 years",
                        "count": "153"
                    },
                    {
                        "item": "70 - 79 years",
                        "count": "153"
                    },
                    {
                        "item": "United States",
                        "count": "153"
                    },
                    {
                        "item": "40 - 49 years",
                        "count": "153"
                    },
                    {
                        "item": "Non-Hispanic, other races and multiple races",
                        "count": "153"
                    },
                    {
                        "item": "Non-Hispanic White, single race",
                        "count": "153"
                    },
                    {
                        "item": "Hispanic or Latino",
                        "count": "153"
                    },
                    {
                        "item": "50 - 59 years",
                        "count": "153"
                    },
                    {
                        "item": "18 - 29 years",
                        "count": "153"
                    },
                    {
                        "item": "Non-Hispanic Black, single race",
                        "count": "153"
                    },
                    {
                        "item": "30 - 39 years",
                        "count": "153"
                    },
                    {
                        "item": "Wyoming",
                        "count": "135"
                    }
                ],
                "smallest": "18 - 29 years",
                "count": "10152",
                "cardinality": "78"
            },
            "format": {}
        },
        {
            "id": 559238948,
            "name": "Phase",
            "dataTypeName": "text",
            "description": "",
            "fieldName": "phase",
            "position": 5,
            "renderTypeName": "text",
            "tableColumnId": 130119080,
            "cachedContents": {
                "non_null": "10152",
                "largest": "3 (Oct 28 \ufffd Dec 21)",
                "null": "0",
                "top": [
                    {
                        "item": "1",
                        "count": "2520"
                    },
                    {
                        "item": "3.2",
                        "count": "1404"
                    },
                    {
                        "item": "3.1",
                        "count": "1296"
                    },
                    {
                        "item": "3 (Jan 6 \ufffd Mar 29)",
                        "count": "1260"
                    },
                    {
                        "item": "2",
                        "count": "1050"
                    },
                    {
                        "item": "3 (Oct 28 \ufffd Dec 21)",
                        "count": "840"
                    },
                    {
                        "item": "3.3",
                        "count": "702"
                    },
                    {
                        "item": "3.4",
                        "count": "702"
                    },
                    {
                        "item": "-1",
                        "count": "378"
                    }
                ],
                "smallest": "1",
                "count": "10152",
                "cardinality": "9"
            },
            "format": {}
        },
        {
            "id": 559238949,
            "name": "Time Period",
            "dataTypeName": "text",
            "description": "",
            "fieldName": "time_period",
            "position": 6,
            "renderTypeName": "text",
            "tableColumnId": 102255555,
            "cachedContents": {
                "non_null": "10152",
                "largest": "9",
                "null": "0",
                "top": [
                    {
                        "item": "1",
                        "count": "588"
                    },
                    {
                        "item": "39",
                        "count": "234"
                    },
                    {
                        "item": "34",
                        "count": "234"
                    },
                    {
                        "item": "40",
                        "count": "234"
                    },
                    {
                        "item": "36",
                        "count": "234"
                    },
                    {
                        "item": "37",
                        "count": "234"
                    },
                    {
                        "item": "43",
                        "count": "234"
                    },
                    {
                        "item": "42",
                        "count": "234"
                    },
                    {
                        "item": "44",
                        "count": "234"
                    },
                    {
                        "item": "38",
                        "count": "234"
                    },
                    {
                        "item": "35",
                        "count": "234"
                    },
                    {
                        "item": "45",
                        "count": "234"
                    },
                    {
                        "item": "41",
                        "count": "234"
                    },
                    {
                        "item": "33",
                        "count": "216"
                    },
                    {
                        "item": "32",
                        "count": "216"
                    },
                    {
                        "item": "30",
                        "count": "216"
                    },
                    {
                        "item": "31",
                        "count": "216"
                    },
                    {
                        "item": "28",
                        "count": "216"
                    },
                    {
                        "item": "29",
                        "count": "216"
                    },
                    {
                        "item": "17",
                        "count": "210"
                    }
                ],
                "smallest": "1",
                "count": "10152",
                "cardinality": "45"
            },
            "format": {}
        },
        {
            "id": 559238950,
            "name": "Time Period Label",
            "dataTypeName": "text",
            "description": "",
            "fieldName": "time_period_label",
            "position": 7,
            "renderTypeName": "text",
            "tableColumnId": 102255551,
            "cachedContents": {
                "non_null": "10152",
                "largest": "Sep 30 - Oct 12, 2020",
                "null": "0",
                "top": [
                    {
                        "item": "Apr 27 - May 9, 2022",
                        "count": "234"
                    },
                    {
                        "item": "Jan 26 - Feb 7, 2022",
                        "count": "234"
                    },
                    {
                        "item": "Sep 15 - Sep 27, 2021",
                        "count": "234"
                    },
                    {
                        "item": "Sep 1 - Sep 13, 2021",
                        "count": "234"
                    },
                    {
                        "item": "Aug 18 - Aug 30, 2021",
                        "count": "234"
                    },
                    {
                        "item": "Mar 30 - Apr 11, 2022",
                        "count": "234"
                    },
                    {
                        "item": "Mar 2 - Mar 14, 2022",
                        "count": "234"
                    },
                    {
                        "item": "Sep 29 - Oct 11, 2021",
                        "count": "234"
                    },
                    {
                        "item": "Dec 29, 2021 - Jan 10, 2022",
                        "count": "234"
                    },
                    {
                        "item": "Dec 1 - Dec 13, 2021",
                        "count": "234"
                    },
                    {
                        "item": "Jul 21 - Aug 2, 2021",
                        "count": "234"
                    },
                    {
                        "item": "Aug 4 - Aug 16, 2021",
                        "count": "234"
                    },
                    {
                        "item": "Jun 23 - Jul 5, 2021",
                        "count": "216"
                    },
                    {
                        "item": "Jun 9 - Jun 21, 2021",
                        "count": "216"
                    },
                    {
                        "item": "Apr 28 - May 10, 2021",
                        "count": "216"
                    },
                    {
                        "item": "May 26 - Jun 7, 2021",
                        "count": "216"
                    },
                    {
                        "item": "Apr 14 - Apr 26, 2021",
                        "count": "216"
                    },
                    {
                        "item": "May 12 - May 24, 2021",
                        "count": "216"
                    },
                    {
                        "item": "June 25 - June 30, 2020",
                        "count": "210"
                    },
                    {
                        "item": "May 28 - June 2, 2020",
                        "count": "210"
                    }
                ],
                "smallest": "Apr 14 - Apr 26, 2021",
                "count": "10152",
                "cardinality": "51"
            },
            "format": {}
        },
        {
            "id": 559238951,
            "name": "Time Period Start Date",
            "dataTypeName": "calendar_date",
            "description": "",
            "fieldName": "time_period_start_date",
            "position": 8,
            "renderTypeName": "calendar_date",
            "tableColumnId": 130119079,
            "cachedContents": {
                "non_null": "10152",
                "largest": "2022-04-27T00:00:00.000",
                "null": "0",
                "top": [
                    {
                        "item": "2021-12-01T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-09-01T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2022-03-30T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-09-29T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2022-03-02T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2022-01-26T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-08-18T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-07-21T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-12-29T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-09-15T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-08-04T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2022-04-27T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-04-14T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2021-06-09T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2021-06-23T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2021-05-12T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2021-04-28T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2021-05-26T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2020-05-07T00:00:00.000",
                        "count": "210"
                    },
                    {
                        "item": "2020-06-11T00:00:00.000",
                        "count": "210"
                    }
                ],
                "smallest": "2020-04-23T00:00:00.000",
                "count": "10152",
                "cardinality": "51"
            },
            "format": {
                "view": "date"
            }
        },
        {
            "id": 559238952,
            "name": "Time Period End Date",
            "dataTypeName": "calendar_date",
            "description": "",
            "fieldName": "time_period_end_date",
            "position": 9,
            "renderTypeName": "calendar_date",
            "tableColumnId": 130119078,
            "cachedContents": {
                "non_null": "10152",
                "largest": "2022-05-09T00:00:00.000",
                "null": "0",
                "top": [
                    {
                        "item": "2022-04-11T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2022-02-07T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-08-30T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2022-05-09T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-08-16T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-12-13T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2022-03-14T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2022-01-10T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-09-13T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-08-02T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-09-27T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-10-11T00:00:00.000",
                        "count": "234"
                    },
                    {
                        "item": "2021-06-21T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2021-05-24T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2021-07-05T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2021-05-10T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2021-04-26T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2021-06-07T00:00:00.000",
                        "count": "216"
                    },
                    {
                        "item": "2020-07-07T00:00:00.000",
                        "count": "210"
                    },
                    {
                        "item": "2020-09-28T00:00:00.000",
                        "count": "210"
                    }
                ],
                "smallest": "2020-05-05T00:00:00.000",
                "count": "10152",
                "cardinality": "51"
            },
            "format": {
                "view": "date"
            }
        },
        {
            "id": 559238953,
            "name": "Value",
            "dataTypeName": "number",
            "description": "",
            "fieldName": "value",
            "position": 10,
            "renderTypeName": "number",
            "tableColumnId": 102255556,
            "cachedContents": {
                "non_null": "9774",
                "largest": "72.2",
                "null": "378",
                "top": [
                    {
                        "item": "31.4",
                        "count": "69"
                    },
                    {
                        "item": "28.7",
                        "count": "66"
                    },
                    {
                        "item": "27.9",
                        "count": "65"
                    },
                    {
                        "item": "28",
                        "count": "64"
                    },
                    {
                        "item": "26.5",
                        "count": "62"
                    },
                    {
                        "item": "27.6",
                        "count": "60"
                    },
                    {
                        "item": "30.6",
                        "count": "60"
                    },
                    {
                        "item": "26.9",
                        "count": "59"
                    },
                    {
                        "item": "31.1",
                        "count": "59"
                    },
                    {
                        "item": "27.4",
                        "count": "58"
                    },
                    {
                        "item": "29.6",
                        "count": "58"
                    },
                    {
                        "item": "26.4",
                        "count": "57"
                    },
                    {
                        "item": "26.7",
                        "count": "57"
                    },
                    {
                        "item": "24.2",
                        "count": "57"
                    },
                    {
                        "item": "25.7",
                        "count": "57"
                    },
                    {
                        "item": "30.4",
                        "count": "56"
                    },
                    {
                        "item": "28.4",
                        "count": "55"
                    },
                    {
                        "item": "27.2",
                        "count": "54"
                    },
                    {
                        "item": "32.3",
                        "count": "53"
                    },
                    {
                        "item": "22.9",
                        "count": "53"
                    }
                ],
                "smallest": "6.4",
                "count": "10152",
                "cardinality": "504"
            },
            "format": {
                "precisionStyle": "standard",
                "precision": "1"
            }
        },
        {
            "id": 559238954,
            "name": "Low CI",
            "dataTypeName": "number",
            "description": "",
            "fieldName": "lowci",
            "position": 11,
            "renderTypeName": "number",
            "tableColumnId": 102255557,
            "cachedContents": {
                "non_null": "9774",
                "largest": "63.5",
                "null": "378",
                "top": [
                    {
                        "item": "25.1",
                        "count": "68"
                    },
                    {
                        "item": "21.3",
                        "count": "65"
                    },
                    {
                        "item": "25.3",
                        "count": "64"
                    },
                    {
                        "item": "26.6",
                        "count": "64"
                    },
                    {
                        "item": "24.2",
                        "count": "62"
                    },
                    {
                        "item": "21.5",
                        "count": "62"
                    },
                    {
                        "item": "21.6",
                        "count": "61"
                    },
                    {
                        "item": "22.2",
                        "count": "61"
                    },
                    {
                        "item": "25.5",
                        "count": "59"
                    },
                    {
                        "item": "21.4",
                        "count": "59"
                    },
                    {
                        "item": "21.7",
                        "count": "59"
                    },
                    {
                        "item": "25.7",
                        "count": "59"
                    },
                    {
                        "item": "24.4",
                        "count": "58"
                    },
                    {
                        "item": "26.4",
                        "count": "58"
                    },
                    {
                        "item": "24.7",
                        "count": "57"
                    },
                    {
                        "item": "23.4",
                        "count": "57"
                    },
                    {
                        "item": "24",
                        "count": "56"
                    },
                    {
                        "item": "24.9",
                        "count": "56"
                    },
                    {
                        "item": "27.8",
                        "count": "56"
                    },
                    {
                        "item": "28.5",
                        "count": "56"
                    }
                ],
                "smallest": "4.9",
                "count": "10152",
                "cardinality": "485"
            },
            "format": {
                "precision": "1"
            }
        },
        {
            "id": 559238955,
            "name": "High CI",
            "dataTypeName": "number",
            "description": "",
            "fieldName": "highci",
            "position": 12,
            "renderTypeName": "number",
            "tableColumnId": 102255554,
            "cachedContents": {
                "non_null": "9774",
                "largest": "80.2",
                "null": "378",
                "top": [
                    {
                        "item": "31.6",
                        "count": "71"
                    },
                    {
                        "item": "33.3",
                        "count": "65"
                    },
                    {
                        "item": "36.8",
                        "count": "63"
                    },
                    {
                        "item": "29.2",
                        "count": "62"
                    },
                    {
                        "item": "31.4",
                        "count": "62"
                    },
                    {
                        "item": "34.4",
                        "count": "61"
                    },
                    {
                        "item": "32",
                        "count": "59"
                    },
                    {
                        "item": "32.1",
                        "count": "59"
                    },
                    {
                        "item": "29",
                        "count": "59"
                    },
                    {
                        "item": "33.2",
                        "count": "59"
                    },
                    {
                        "item": "29.3",
                        "count": "59"
                    },
                    {
                        "item": "33.5",
                        "count": "58"
                    },
                    {
                        "item": "32.3",
                        "count": "58"
                    },
                    {
                        "item": "31",
                        "count": "57"
                    },
                    {
                        "item": "35.1",
                        "count": "57"
                    },
                    {
                        "item": "35.7",
                        "count": "56"
                    },
                    {
                        "item": "31.1",
                        "count": "55"
                    },
                    {
                        "item": "31.2",
                        "count": "54"
                    },
                    {
                        "item": "39.2",
                        "count": "54"
                    },
                    {
                        "item": "31.9",
                        "count": "54"
                    }
                ],
                "smallest": "7.9",
                "count": "10152",
                "cardinality": "521"
            },
            "format": {
                "precision": "1"
            }
        },
        {
            "id": 559238956,
            "name": "Confidence Interval",
            "dataTypeName": "text",
            "description": "",
            "fieldName": "confidence_interval",
            "position": 13,
            "renderTypeName": "text",
            "tableColumnId": 102255552,
            "cachedContents": {
                "non_null": "9774",
                "largest": "9.9 - 17.6",
                "null": "378",
                "top": [
                    {
                        "item": "22.1 - 31.0",
                        "count": "6"
                    },
                    {
                        "item": "27.2 - 35.9",
                        "count": "5"
                    },
                    {
                        "item": "25.0 - 33.3",
                        "count": "5"
                    },
                    {
                        "item": "27.2 - 35.1",
                        "count": "5"
                    },
                    {
                        "item": "22.2 - 30.2",
                        "count": "4"
                    },
                    {
                        "item": "22.5 - 31.6",
                        "count": "4"
                    },
                    {
                        "item": "21.7 - 29.7",
                        "count": "4"
                    },
                    {
                        "item": "19.2 - 27.5",
                        "count": "4"
                    },
                    {
                        "item": "25.7 - 35.5",
                        "count": "4"
                    },
                    {
                        "item": "21.2 - 22.8",
                        "count": "4"
                    },
                    {
                        "item": "34.1 - 43.0",
                        "count": "4"
                    },
                    {
                        "item": "22.3 - 30.5",
                        "count": "4"
                    },
                    {
                        "item": "24.2 - 31.9",
                        "count": "4"
                    },
                    {
                        "item": "28.4 - 36.3",
                        "count": "4"
                    },
                    {
                        "item": "23.5 - 31.4",
                        "count": "4"
                    },
                    {
                        "item": "22.2 - 31.2",
                        "count": "4"
                    },
                    {
                        "item": "23.6 - 32.2",
                        "count": "4"
                    },
                    {
                        "item": "23.4 - 33.2",
                        "count": "4"
                    },
                    {
                        "item": "23.4 - 29.9",
                        "count": "4"
                    },
                    {
                        "item": "23.7 - 33.0",
                        "count": "4"
                    }
                ],
                "smallest": "10.0 - 12.9",
                "count": "10152",
                "cardinality": "8217"
            },
            "format": {}
        },
        {
            "id": 559238957,
            "name": "Quartile Range",
            "dataTypeName": "text",
            "description": "",
            "fieldName": "quartile_range",
            "position": 14,
            "renderTypeName": "text",
            "tableColumnId": 102255553,
            "cachedContents": {
                "non_null": "6885",
                "largest": "44.4-49.7",
                "null": "3267",
                "top": [
                    {
                        "item": "21.6-26.2",
                        "count": "26"
                    },
                    {
                        "item": "23.2-27.3",
                        "count": "26"
                    },
                    {
                        "item": "33.2-35.5",
                        "count": "25"
                    },
                    {
                        "item": "23.9-26.0",
                        "count": "25"
                    },
                    {
                        "item": "27.4-29.8",
                        "count": "25"
                    },
                    {
                        "item": "26.9-31.8",
                        "count": "24"
                    },
                    {
                        "item": "31.8-37.1",
                        "count": "23"
                    },
                    {
                        "item": "21.0-23.8",
                        "count": "16"
                    },
                    {
                        "item": "14.6-19.0",
                        "count": "15"
                    },
                    {
                        "item": "29.0-32.3",
                        "count": "15"
                    },
                    {
                        "item": "29.7-31.7",
                        "count": "15"
                    },
                    {
                        "item": "35.0-37.9",
                        "count": "15"
                    },
                    {
                        "item": "29.5-32.2",
                        "count": "15"
                    },
                    {
                        "item": "22.6-24.1",
                        "count": "15"
                    },
                    {
                        "item": "20.8-25.4",
                        "count": "15"
                    },
                    {
                        "item": "25.0-30.7",
                        "count": "15"
                    },
                    {
                        "item": "19.5-22.5",
                        "count": "15"
                    },
                    {
                        "item": "25.1-28.7",
                        "count": "14"
                    },
                    {
                        "item": "28.1-30.1",
                        "count": "14"
                    },
                    {
                        "item": "40.5-42.8",
                        "count": "14"
                    }
                ],
                "smallest": "10.6-19.5",
                "count": "10152",
                "cardinality": "533"
            },
            "format": {}
        }
    ],
    "grants": [
        {
            "inherited": false,
            "type": "viewer",
            "flags": [
                "public"
            ]
        }
    ],
    "license": {
        "name": "Public Domain U.S. Government",
        "termsLink": "https://www.usa.gov/government-works"
    },
    "metadata": {
        "custom_fields": {
            "Data Quality": {
                "Geospatial Resolution": "State",
                "Suggested Citation": "National Center for Health Statistics. Indicators of Anxiety or Depression Based on Reported Frequency of Symptoms During Last 7 Days. Data accessed [Last accessed date]. Available from: https://data.cdc.gov/d/8pt5-q6wp."
            },
            "Common Core": {
                "Contact Email": "cdcinfo@cdc.gov",
                "Homepage": "https://www.cdc.gov/nchs/covid19/pulse/mental-health.htm",
                "Language": "en-US",
                "Contact Name": "National Center for Health Statistics",
                "Update Frequency": "R/P2W",
                "Temporal Applicability": "2020-08-19/2022-05-09",
                "Issued": "2020-05-20",
                "Program Code": "009:020",
                "Geographic Coverage": "US",
                "Publisher": "National Center for Health Statistics",
                "Bureau Code": "009:20",
                "Public Access Level": "public"
            }
        },
        "rowLabel": "Estimate of anxiety or depression",
        "availableDisplayTypes": [
            "table",
            "fatrow",
            "page"
        ]
    },
    "owner": {
        "id": "ki96-txhe",
        "displayName": "NCHS",
        "profileImageUrlLarge": "/api/users/ki96-txhe/profile_images/LARGE",
        "profileImageUrlMedium": "/api/users/ki96-txhe/profile_images/THUMB",
        "profileImageUrlSmall": "/api/users/ki96-txhe/profile_images/TINY",
        "screenName": "NCHS",
        "type": "interactive",
        "flags": [
            "acceptedEula",
            "mayBeStoriesCoOwner"
        ]
    },
    "query": {},
    "rights": [
        "read"
    ],
    "tableAuthor": {
        "id": "ki96-txhe",
        "displayName": "NCHS",
        "profileImageUrlLarge": "/api/users/ki96-txhe/profile_images/LARGE",
        "profileImageUrlMedium": "/api/users/ki96-txhe/profile_images/THUMB",
        "profileImageUrlSmall": "/api/users/ki96-txhe/profile_images/TINY",
        "screenName": "NCHS",
        "type": "interactive",
        "flags": [
            "acceptedEula",
            "mayBeStoriesCoOwner"
        ]
    },
    "tags": [
        "depression",
        "anxiety",
        "covid-19"
    ],
    "flags": [
        "default",
        "ownerMayBeContacted",
        "restorable",
        "restorePossibleForType"
    ]
}

simonw commented 2 years ago

Problem: the get_metadata() plugin hook currently doesn't offer an asyncio mechanism, so it's hard to query the database table on-demand for metadata: https://docs.datasette.io/en/stable/plugin_hooks.html#get-metadata-datasette-key-database-table

get_metadata(datasette, key, database, table)

simonw commented 2 years ago

Simplest workaround is probably to populate an in-memory Python data structure with a copy of the contents of that table, and refresh it every X seconds (maybe 10s?) - but force a refresh of it any time new Socrata is imported.

simonw commented 2 years ago

I added a get_metadata hook with basic logging and saw this:

get_metadata database= data table= None key= queries
get_metadata database= data table= None key= tables
get_metadata database= data table= None key= allow
get_metadata database= None table= None key= allow
get_metadata database= data table= None key= tables
get_metadata database= None table= None key= databases
get_metadata database= None table= None key= databases
get_metadata database= None table= None key= databases
get_metadata database= data table= None key= allow_sql
get_metadata database= None table= None key= allow_sql
get_metadata database= None table= None key= source
get_metadata database= None table= None key= source_url
get_metadata database= None table= None key= license
get_metadata database= None table= None key= license_url
get_metadata database= None table= None key= databases
get_metadata database= None table= None key= databases
get_metadata database= None table= None key= databases
get_metadata database= None table= None key= source
get_metadata database= None table= None key= source_url
get_metadata database= None table= None key= license
get_metadata database= None table= None key= license_url
get_metadata database= None table= None key= about
get_metadata database= None table= None key= about_url
get_metadata database= None table= None key= extra_css_urls
get_metadata database= None table= None key= extra_js_urls
INFO:     127.0.0.1:57808 - "GET /data/socrata_24uj_dj8v HTTP/1.1" 200 OK

Plugin:

@hookimpl
def get_metadata(datasette, database, table, key):
    print("get_metadata database=", database, "table=", table, "key=", key)

I'm surprised that table=None for every one of those and database=None for most of them. Is this a bug?

simonw commented 2 years ago

I'm not even going to do that every-10-s thing - I will trust that the only changes come from Datasette itself, so I'll update the in-memory data structure only on startup or when someone imports new data.

simonw commented 2 years ago

Got this prototype working:

simonw commented 2 years ago

Got license working too:

simonw commented 2 years ago

I'm not even going to do that every-10-s thing - I will trust that the only changes come from Datasette itself, so I'll update the in-memory data structure only on startup or when someone imports new data.

Had an alternative idea: ASGI middleware to intercept all POST requests and use those to trigger an update too.

But that could have a negative impact on performance when combined with features I'm planning where large amounts of data gets imported into Datasette via multiple POST requests (also the enrichments feature I'm considering) - so for the moment I'm just going to run it on known modifications to the table by this plugin.

simonw commented 2 years ago

Sometimes metadata has HTML in it:

I don't want to trust this fully, but I could run it through Bleach and put it in description_html.

simonw commented 2 years ago

Here's my prototype:

diff --git a/datasette_socrata/__init__.py b/datasette_socrata/__init__.py
index 9a8a81e..f0cde72 100644
--- a/datasette_socrata/__init__.py
+++ b/datasette_socrata/__init__.py
@@ -211,6 +211,7 @@ async def import_socrata(request, datasette):
             replace=True,
         )
     )
+    await refresh_in_memory_socrata_metadata(datasette)

     # Now start the import, in a task which runs after this request has returned
     table_name = "socrata_" + id.replace("-", "_")
@@ -418,3 +419,61 @@ def extra_body_script(view_name, table, database, datasette):
                 )
             ),
         )
+
+
+async def refresh_in_memory_socrata_metadata(datasette):
+    if not hasattr(datasette, "_socrata_metadata"):
+        datasette._socrata_metadata = {}
+    for name, db in datasette.databases.items():
+        # Does it have a socrata_imports table?
+        if "socrata_imports" in await db.table_names():
+            table_metadata = {
+                row["id"]: dict(row, metadata=json.loads(row["metadata"]))
+                for row in await db.execute("select * from socrata_imports")
+            }
+            datasette._socrata_metadata[name] = table_metadata
+
+
+# Populate datasette._socrata_metadata on startup
+@hookimpl
+def startup(datasette):
+    async def inner():
+        await refresh_in_memory_socrata_metadata(datasette)
+
+    return inner
+
+
+@hookimpl
+def get_metadata(datasette):
+    if not hasattr(datasette, "_socrata_metadata"):
+        return None
+    databases = {}
+    for database in datasette._socrata_metadata.keys():
+        tables = {}
+        for table_id, info in datasette._socrata_metadata[database].items():
+            table_metadata = {
+                "title": info["name"],
+                "source": info["metadata"].get("attribution"),
+                "source_url": info["url"],
+            }
+            description = info["metadata"].get("description")
+            if description:
+                table_metadata["description"] = description
+            column_descriptions = {
+                c["name"]: c["description"]
+                for c in info["metadata"].get("columns")
+                if c.get("description")
+            }
+            if column_descriptions:
+                table_metadata["columns"] = column_descriptions
+            if info["metadata"].get("license"):
+                license_name = info["metadata"]["license"].get("name")
+                license_url = info["metadata"]["license"].get("termsLink")
+                if license_name and license_url:
+                    table_metadata["license"] = license_name
+                    table_metadata["license_url"] = license_url
+            tables["socrata_{}".format(table_id.replace("-", "_"))] = table_metadata
+        databases[database] = {
+            "tables": tables,
+        }
+    return {"databases": databases}
diff --git a/datasette_socrata/templates/datasette_socrata.html b/datasette_socrata/templates/datasette_socrata.html
index a005618..7da5f4c 100644
--- a/datasette_socrata/templates/datasette_socrata.html
+++ b/datasette_socrata/templates/datasette_socrata.html
@@ -42,7 +42,9 @@
     </select></label>
   </p>
   {% else %}
-    <p>Data will be imported into the <strong>{{ databases[0] }}</strong> database.</p>
+    {% if databases %}
+      <p>Data will be imported into the <strong>{{ databases[0] }}</strong> database.</p>
+    {% endif %}
   {% endif %}
 </form>

simonw commented 2 years ago

I can improve this prototype by moving all of that get_metadata logic into refresh_in_memory_socrata_metadata().

simonw commented 2 years ago

Datasette bug: the metadata title of the tables is not shown on the database page:

simonw / datasette-socrata

Table metadata should display using data in socrata_imports table #11