BigQuery出现Expected end of input but got keyword INSERT报错如何解决

• BigQuery默认以单条语句模式解析执行输入的SQL，你的代码中一次性包含了CREATE TABLE、INSERT、SELECT三条独立的SQL语句，且没有添加语句分隔符，SQL解析器执行完CREATE TABLE逻辑后预期输入结束，就遇到了第11行的INSERT关键字，因此触发该语法报错。
• 你的CREATE TABLE语句末尾缺少分号;作为结束标识，多语句同时执行时必须用分号分隔每条独立语句。

方案1：单条拆分执行（调试场景适用） #

依次单独执行CREATE TABLE、INSERT、SELECT三条语句，每次仅提交一条语句运行，无需调整现有代码语法。

方案2：添加分隔符一次性执行 #

在每条独立语句的末尾添加分号;作为结束标识，调整后的完整代码如下：

create table percentpopulationvaccinated
(
    continent string,
    Location string,
    Date datetime,
    population numeric,
    new_vaccinations numeric,
    peoplevaccinated numeric
);

insert into percentpopulationvaccinated 
    select 
        dea.continent, dea.location, dea.date, dea.population,  
        new_vaccinations,
        sum(vac.new_vaccinations) over (partition by dea.location order by dea.location, dea.date) as peoplevaccinated
    from 
        my-protfolio-324718.sql_code.covid_deaths dea
    join 
        my-protfolio-324718.sql_code.covid_vac vac on dea.location = vac.location
                                                   and dea.date = vac.date;

select 
    *,
    (peoplevaccinated / population) * 100 as vacc_percent
from 
    percentpopulationvaccinated;

优化方案（推荐） #

可以直接使用BigQuery支持的CTAS（CREATE TABLE AS SELECT）语法，一步完成表创建和数据插入，代码更简洁也不会出现多语句分隔问题：

create or replace table percentpopulationvaccinated as
select 
    dea.continent, 
    dea.location, 
    dea.date, 
    dea.population,  
    vac.new_vaccinations,
    sum(vac.new_vaccinations) over (partition by dea.location order by dea.location, dea.date) as peoplevaccinated,
    (sum(vac.new_vaccinations) over (partition by dea.location order by dea.location, dea.date)/dea.population)*100 as vacc_percent
from 
    my-protfolio-324718.sql_code.covid_deaths dea
join 
    my-protfolio-324718.sql_code.covid_vac vac 
    on dea.location = vac.location and dea.date = vac.date;

-- 直接查询结果
select * from percentpopulationvaccinated;

内容的提问来源于stack exchange，提问作者Aman Kumar