I have a 16-year-old son. I’m in my early 30s (had him very young) and a professional footballer. My son also dreams of becoming a successful footballer (he’s been playing since he was 6), but he’s just… not great. He’s good, but not great - and in this extremely competitive industry you need to be at least great in order to even stand a chance. So I told him, as someone who’s been doing this for a very, very long time & is active in this sphere, that he should find another, more attainable dream. He took it as me not believing in him, but I’m just objective and realistic.
You should support your kid, even if you’re not convinced of his chances of making it. Anything else is just going to permanently damage your relationship. The best you can do is make sure he has a plan B. If he’s good in school he’s well on his way to that, so not much to worry about.